DATA: IMPORT & EXPORT IN MICROSOFT SQL SERVER

 Audience: This Blog was intended for 6months of experience with Microsoft SQL Server.

Tools for Import & Export Data from Microsoft SQL Server:

There are 3 ways of data importing & exporting tools available in MSSQL Server.

  1. Transact-SQL
  2. Command - Line Tools
  3. Wizards

Short introduction of each method:

Transact-SQL:
Is a procedural programming language and extension of standard SQL data manipulation statements developed by Microsoft. Which can be used for data import and export mechanisms.

Command-Line tools:
  • There are serval MSSQL CLI tools available for data manipulation using command prompt. CLI tools examples as below:
    • BCP Utility
    • SQLCMD Utility
    • SQL Server Application Utility
    • SQL Shell Cmdlets for PowerShell 
    • So on....
  • These tools needs to be explicitly installed on different Operating Systems like Windows, MacOS and Linux.
Wizards:
Wizards are like GUI developed to ease of running rich scripts to provide access to SQL for developers and database administrators of all skill levels.

Examples: 
  • SSMS - SQL Server Management Studio
  • SSDT - SQL Server Data Tools
  • SSIS - SQL Server Integration Services
  • A custom data import services can also developed.
  • So on...
Note: With SSDT & SSIS are very powerful tools for importing and exporting data using variety of data sources and destinations.

Data Formats used for importing & exporting into MSSQL:

  • Flat Files
  • Excel Files
  • JSON Files
  • XML Files
  • Database to Database
  • From Cloud Services

Examples:

  • Flat Files: Text Files
  • Relational Database to Relational Database: 
    • In same server instance
    • To different Server instance
    • From Microsoft SQL Server to MySQL Server (Oracle Variant)
  • Cloud Services:
    • Azure Blob Storage

Labs:

Links to Labs:

Importing Data from Flat Files

Comments

Post a Comment

Popular posts from this blog

DATA: IMPORT FROM EXCEL FILE TO MICROSOFT SQL SERVER

Image
  Audience: This Blog was intended for 6months of experience with Microsoft SQL Server. Importing Data to Microsoft SQL Server from Excel File: Prerequisites: Excel File with data to be imported. SQL Server Edition: Any Edition (Developer Edition, Evaluation, Enterprise, Standard, Express) Tools Used:  SQL Server Import and Export Wizard. SQL Server Integration Tools. Note: To run successfully this example, SSIS ( SQL Server Integration Services) to be installed in local computer. Procedure on importing data: Open a blank excel file  Enter below data into file (this data will be added to table) Change the name of the sheet to  WizardWalkthrough  (highlighted in red color in below screenshot) Note: This sheet name will be the name of the table in database. Open SQL Server Import and Export Data Wizard. Select Data Source (here Excel File - WizardWalkthrough.xlsx) and click next Select Destination (Microsoft OLE DB Driver for SQL Server) Note: This only will work ...
Read more

Microsoft SQL Server Structure

Image
TLDR: This article provides information about Microsoft SQL Server structures which includes information about components which were unified to create a workable database. Components of Microsoft SQL Server: SQL Server Instance  Database  Schema - owner of database objects like tables, views and stored procedures Tables, Views and Stored Procedures.
Read more