sql tasks import data

PASS Data Community Summit

Watch sessions online for free

14-17 November

23 October 2023

Exporting and Importing Data into SQL Server Using Files

There are plenty of applications and tools available that allow for the movement of data in and out of SQL Server. Some tools are built by Microsoft, such as SSIS or Azure Data Factory. Others are created by third parties, such as Databricks or Snowflake. Still other available options make use of SQL Server features and rely on our own ability to write efficient code to get data from point A to point B.

Before diving into the specific solution presented in this article, there is value in recognizing that different data movement methods are better for different situations and data profiles. The following is a (brief) overview of when different types of tools might be most useful:

• Azure Data Factory, Azure Synapse Link : Ideal for data sources already in Azure, where these tools are conveniently available and file system/3 rd party app access may be limited or inconvenient.
• SSIS : Convenient for on-premises SQL Server workloads or scenarios where SSIS is already used and an organization has existing expertise using it.
• Third Party Tools : There are many out there and their use is ideal when an organization is already invested in their applications or architecture. Mixing and matching ecosystems can be challenging, expensive, and require more overhead to spec-out security and data flows.
• Linked Servers : This is insanely convenient when moving data between two SQL Servers, but does require a secure connection between those servers. Linked servers are not ideal for large data sets and trying to efficiently run complex queries cross-server can yield unexpected results.

For the purposes of this article, we will focus solely on the task of moving a data set from one server to another. Topics such as ETL, ELT, data warehousing, data lakes, etc…are important and relevant to data movement, but out of scope for a focused discussion such as this.

Why (and Why Not to) Use Files?

The primary benefits of exporting data to files are:

• File portability . Once written, a file may be compressed, moved, copied, backed up, or otherwise manipulated freely without any impact on SQL Server.
• Bulk Loading . When reading a file into SQL Server, it can be bulk-loaded, which uses a minimally-logged process to import data faster while reducing the impact on the transaction log.
• Security . The only security need is a storage location to write the file to from the source server and a storage location to read the file from on the target server. There is no need for connections to other servers, services, or apps.
• Economy . In general, file storage and movement is inexpensive, whereas IO within SQL Server or other cloud services can be more costly. This varies from service-to-service, but for large data sets, data movement can quickly become a non-trivial cost.

The benefits above are also reasons to not use files. If you have no easy way to move the source file to the target destination, then portability provides no value. Similarly, if data loads need to be fully-logged for posterity or detailed, recovery (such as restoring to a point-in-time), then bulk-loading cannot be used. Lastly, if the resources needed to write/read a file from storage are not available for either the source or target server, then those would also be nonstarters.

If unsure of what method is best, consider testing the most feasible ones available to determine which are the fastest and most cost-effective. Scalability is also important. If you believe that the five servers you move files from may one day become one hundred servers, then consider if processes built now will scale up over time.

A Note on the Methods I will Use

Because there are many ways to import and export data, you may be quick to ask why I chose these specific methods. The choice of methods and code was based on two important factors:

In terms of security, it was important to avoid xp_cmdshell or any other extended stored procedure that could provide security weak points by unnecessarily linking SQL Server to the file system. Enabling a feature like that creates a permanent security hole that is best left avoided. In this article I will use PowerShell to export data, which can read data from a table into a file and do so without needing any changes to SQL Server security. Similarly, to import data into SQL Server, I will use OPENROWSET to read a file, and then insert that data into a table without any other special security considerations. 

Speed is also quite important here. Presumably, loading files like this will often be associated with analytics, logging, or other processes that tend to move large volumes of data. Ideally, these processes should be minimally logged and not consume excessive system resources. Writing data out to a file requires only reading the source table. There is no need for any SQL Server logging or writes, and once the data has been written to the CSV, our work on the source database server is complete without any cleanup or additional actions needed. Reading the file via OPENROWSET can take advantage of a minimally-logged bulk insert, which is key to performance here. Not needing to log the details of every row inserted into the target table will greatly improve the speed of this process. As a bonus, there will be no transaction log bloat, which will also avoid log file growth and potentially large transaction log backups.

Generating CSV Files from SQL Server Queries

Consider a query that needs to capture sales details for a given month. The results need to be imported automatically into another server. Here, we will walk through one way to automate this using the query, some PowerShell, and a SQL Server Agent job to execute the code.

The following is a query that pulls nine columns from a handful of tables in WideWorldImportersDW (which can be downloaded here from learn.microsof.com ):

The month bounded by the WHERE clause may vary over time, but the general shape of the data will remain static. The results show us what this data looks like:

This is relatively straightforward data: Some dates, integers, decimals, and strings. Nothing unusual that might require special consideration. Note that file encoding may become relevant if the data source contains Unicode characters.

Create a New SQL Server Agent Job

To start, let’s create a new SQL Server Agent Job:

The details are entirely up to you and the conventions your database environment follows for names, categories, etc…For the moment, no schedule will be created as we can test this manually. Note that sa is used for the job Owner. Feel free to substitute it with any other job owner. (For more information, check out this blog post from Brent Ozar.)

The SQL Server Agent login will still be the one that is used to execute the job, regardless of its owner.

Next, go to the General pane, and click on the “New” button. Then choose PowerShell as the job type:

This is where the job creation pauses so that a PowerShell script can be written and tested. The steps that need to be accomplished are:

• Define the SQL Server connection and file destination.
• Define the query.
• Define the file name and format.
• Execute code to export the contents of the query to the file based on the specifications defined above.

Note that it is possible to export data using xp_cmdshell or some other SQL Server extended stored procedure, but those options are avoided due to the security concerns they raise. PowerShell can be executed from a SQL Server Agent job, a Windows task, or via a similar process.

To begin, the following code will outline the query source and destination:

The value for $SQLServer may need to be adjusted if a named instance is being accessed or if the SQL Server is not local to this machine. While the comma is used as the delimiter, other characters may be chosen. The date format is arbitrary and is used later when naming the file. Feel free to adjust it as needed or remove it if a timestamp is unneeded. Lastly, $FilePath is a local folder on my machine that the file can be exported to. This can also be adjusted to whatever location makes the most sense to export the CSV file to.

With the parameters for this process defined, the query to be executed can be added:

Note that there is no need to double the apostrophes as the T-SQL is entered in double-quotes (and it is PowerShell, not dynamic T-SQL). Next, the file destination needs to be defined and the query prepared for execution:

As with the prior code, there is latitude here for customization. The file name and extension may be adjusted as needed to make your version of this process as easy to manage as possible. Ideally, files should be easy to identify via automated processes so it is simple to move/read them. In addition, regular cleanup of old files may be a helpful step as well. Lastly, the data set can be exported to a CSV:

The code above can be pasted into the SQL Server Agent job step from above. (You can download the code from the Simple-Talk website here ).

The job step and job can be saved. To test the newly created job, it will be run manually (if it does not work for any reason, editing the code in a tool like Visual Studio Code can be very helpful to find the issue. Common issues are security related, like the service account not having direct access to the directory you are exporting to.):

After the job succeeds, the target directory can be checked to confirm that the file was generated:

PowerShell may also be configured to run using the Windows Task Scheduler, which would allow it to run independently of SQL Server, without the need for a SQL Server Agent job. This may or may not be convenient but is an option that is available. This can be especially useful if you are working with an Express Edition server.

Note that there are many other ways to generate files from SQL Server that vary in process, simplicity, and need for operator intervention. You are welcome to use another method if it meets the needs of your database environment more effectively.

File Cleanup

If automatic cleanup is desired for CSV files, there are a couple of scenarios that can be handled here. First, if there is a need to remove empty CSVs, the following code can do that:

This is useful if it is possible for empty files to be created and it is preferable to delete them than to move them around between file systems or servers and try to process them anyway.

Similarly, if there is no need to keep CSV files past a given retention period, a command like this can remove files older than a set number of days:

This removes all CSV files older than 7 days and can be adjusted to whatever retention period is needed.

Automatic file cleanup of some sort is a good idea. Because forever is a long time to retain files! PowerShell can handle any retention scenario that can be dreamed up, whether similar to the examples provided here or not.

There are many ways to implement cleanup, such as a step added onto this SQL Server Agent job, a new job, or a Windows scheduled task. The method you choose should be based on your standards and what is easiest to maintain over time. Adding a step to this job is likely the simplest way to add cleanup, but not the only valid way.

Importing Data from CSV Files into SQL Server

Once created, data files can be compressed and moved to a target location, wherever that happens to be. PowerShell is an ideal tool for command-line operations, though you may have your own tools to accomplish common file manipulation tasks.

When complete, a file or set of files will now reside on a new server and be ready for import into a database. There are many ways to accomplish this task, each with strengths and weaknesses. The primary challenge when importing data into SQL Server using any method is aligning the data types in the source data set (the CSV files) with the data types in the target table. Anyone that has experience using the Data Import/Export Wizard in SQL Server Management Studio has undoubtedly felt the pain of mismatched data types, unexpected NULLs, and data truncation errors.

The first step to getting data imported into SQL Server is to have a table available that the data can be loaded into. It is critical that the data types in this table match the data types used earlier exactly. Mismatched data types can lead to data truncation, errors, bad data, and perhaps worst of all, a very difficult task of figuring out exactly where such errors are coming from in your source data!

This is a step that is worth double and triple-checking for accuracy! The following is the table structure that will be used for our staging table:

Note that while the column names match those used earlier, they do not have to match. A mapping can be created between source and target data if there is a need for differing naming conventions. That being said, there is value in consistency, and I recommend that column names are kept in sync between source data set, CSV file, and target table.

For our data import process, we will create an XML format file template up-front that will be used by OPENROWSET when data is read from the file. A prefabricated template provides some significant benefits, as well as a few drawbacks, including:

• Guaranteed data type matching from source to target.
• Easy mapping of columns within the source data to target table.
• Flexibility to customize data type/length/terminator details extensively.
• Schema changes in the source data must be reconciled with the format file prior to importing data.
• Mistakes in the format file will lead to errors

Note that that while format file mistakes will lead to errors when importing data, this is not completely a bad thing. Many operators would prefer an error to bad data or silent conversion problems.

Creating a Format File Template

We will create a format file template using XML. The file will be broken into three sections:

• Column size/terminator definitions
• Column names/data types

The header contains basic XML definition information and is universal to this format file, regardless of the tables/columns imported:

The next block of XML contains a row per column that describes field terminators, lengths, and collation:

The numbers provided for FIELD ID should match the column order from the source table and CSV file. While it is possible to move these around, the resulting confusion is not worth the effort. The parts of this code that need to be modified with each different CSV file are the TERMINATOR , MAX_LENGTH , and COLLATION .

TERMINATOR will typically be a comma (for a CSV file), though it may be adjusted to another separator if the file was generated using another process.

MAX_LENGTH defines the maximum length that the column can be. If the column is a string, then the maximum length is whatever the column length is as defined in the source data set. For other data types, this maximum length should the most characters a column could consume. There is no need to be completely precise here on field length. A date may consume 10 characters exactly ( MM/DD/YYYY ), but if 20 or 50 or 100 is used, the performance difference will be negligible. Feel free to estimate, so long as the numbers used are greater than or equal to the actual column maximum length.

COLLATION is only used for columns that have a collation associated with them, which means only string/character columns. This includes CHAR , NCHAR , VARCHAR , NVARCHAR , TEXT , and NTEXT data types. If you work in an environment with many different collations, then there may be a need to add some additional code into the T-SQL code presented in the next section to handle that and ensure there are no collation conflicts when the file is read into SQL Server. Using an explicit or dynamic column list with COLLATE DATABASE_DEFAULT or something similar would accomplish the task effectively, if needed.

The final section of XML includes a row per column and additional details of the target table:

The data types may be a bit unfamiliar to those exposed to data types exclusively within databases. The following documentation assists in mapping SQL Server data types to the CLR data types used here:

https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration-database-objects-types-net-framework/mapping-clr-parameter-data?view=sql-server-ver16

While not wholly intuitive, the leap between these data types is not complicated.

As with the prior section of XML, ensure that the COLUMN SOURCE values match up with the FIELD ID values defined previously. NAME is the exact column name for the target table where this data will be imported to. Lastly, the type is the CLR data type discussed above.

Finally, the file should be concluded with a closing tag:

While creating a format file for the first time may be challenging, mistakes are relatively easy to resolve. For example, if the Quantity column were defined as a SQLDATETIME , instead of a SQLINT , the following error would be returned when importing the data:

Msg 257, Level 16, State 3, Line 40 Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

The error immediately implies that we should check DATETIME values and determine where a DATETIME is being mapped to an INT . In the example above, there are no other DATETIME columns, so locating the mistake is relatively painless.

Note: the XML file in its complete version is located in the downloads from the Simple-Talk website here ).

A Note on Compression

If the CSV file that was generated earlier in this article is to be moved from server-to-server, then there is value in compressing it prior to moving it. Many popular utilities exist that can compress files, such as Gzip or 7Zip, or Windows’ built-in compression.

Regardless of whether the source table was compressed in SQL Server, the CSV file that is generated will not be. The larger the file gets, the more space that will be saved, reducing the bandwidth needed to move the file, and ultimately reducing the time needed to move it.

Importing Data to SQL Server

With the building blocks of a target table, CSV file, and format file complete, T-SQL may be written to import the CSV file. The script is relatively short and simple and leans heavily on the work that we have already done. Note that some parameters are defined at the beginning of the script and spliced into a dynamic SQL statement. If your file name/location never changes, you may use static T-SQL instead, and reduce the size of this code to 7 lines:

Note, you can download the .CSV file on the Simple-Talk website here , if you only want to work on the importing code).

Some details about this code that can be customized:

• The file location, file name, schema name, and table name can all be adjusted to match the file that will be imported. If the table name, date, and server are all known, then those 3 strings can be concatenated into the CSV name with a bit of additional work.
• FIRSTROW depends on whether header data exists in the CSV file. Our file contains a single row at the start of the file with column names, therefore the first data row is 2.
• FORMATFILE is the location/name of the format file. Here, I chose to match the format file name and target table name. You may adjust this if convenient to be something else.
• FORMAT will be CSV, if CSV is the source file format.

Using BULK with OPENROWSET allows for a minimally logged bulk import of data into the target table. This is ideal for large analytic data loads, where full logging can be time-consuming and resource intensive. There are prerequisites for minimal logging, which can be reviewed here: https://learn.microsoft.com/en-us/sql/relational-databases/import-export/prerequisites-for-minimal-logging-in-bulk-import?view=sql-server-ver16 .

When executed, the result is a speedy import of 5,196 rows and a success message:

Lastly, we can review the data that was imported into SQL Server:

The first time that data is imported from files, there is value in manually examining some of the data to ensure that it looks correct. Special attention should be paid to:

• NULL values
• Truncation/data length

The data set used in this article intentionally had no NULL values in any of the columns. If the source data set can be formatted effectively to remove NULL values and clean up abnormalities, then the import of data later on will be smoother and easier to manage.

It is far easier to debug data issues in the source data than those that occur downstream after data has been exported, compressed, moved, and imported.

If NULL is not removed from the source data (or somehow managed), then it will appear as the string “NULL” in the CSV. If that NULL string is mixed into an INT , DATE , or other non-string column, then it may result in unexpected errors or unusual data when imported.

To summarize: Agree on a way to manage data types, sizes, and NULL prior to exporting data.

The importing and exporting of data is likely one of the greatest administrative hassles to face data professionals. The number of ways in which things can go wrong is innumerable. Having a well-architected, tested, and reliable process to manage these processes can save immense time as well as greatly improve data quality.

Many of the manual GUI tools for moving data around are time-intensive, error-prone, and can lead to invisible bad data that is not detected until it is too late. When these processes can be automated, then the removal of the human factor can further speed up data generation/movement.

This article walked through a step-by-step approach for exporting data from SQL Server and then importing it into another SQL Server. There are many ways to accomplish this task. Feel free to experiment and customize to determine which methods and details are most effective for the tasks that challenge you.

All files and code used in these demos are provided for you to review and learn from. If you have any questions or ideas for future improvements, feel free to share with me anytime!

Subscribe for more articles

Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed.

Rate this article

Edward Pollack

Ed Pollack has 20+ years of experience in database and systems administration, which has developed his passion for performance optimization, database design, and making things go faster. He has spoken at many SQLSaturdays, 24 Hours of PASS, and PASS Summit. This led him to organize SQLSaturday Albany, which has become an annual event for New York’s Capital Region. In his free time, Ed enjoys video games, traveling, cooking exceptionally spicy foods, and hanging out with his amazing wife and sons.

Follow Edward Pollack via

View all articles by Edward Pollack

Load comments

Related articles

Don’t use DISTINCT as a “join-fixer”

• T-SQL Programming

• Wise Owl Training

Scheduling Data Imports in SQL Server

BLOGS BY TOPIC ▼

• Blog home page (475)
• Excel / VBA (117)
• Power platform (112)
• SQL and SQL Server (129)
• Programming (128)
• General (107)

BLOGS BY AUTHOR ▼

• Andrew Gould (56)
• Andy Brown (381)
• Jenny Brown (1)
• Michael Allsop (3)
• Sam Lowrie (32)
• Shaun Wantling (2)

If you frequently import data into a SQL Server database from the same source you'll probably be sick of going through the import wizard again and again. So why not learn how to schedule an automatic import of your data using SSIS packages and the SQL Server Agent? This blog explains how to do exactly that!

• Scheduling Data Imports in SQL Server (this blog)
• Using the Import Wizard in SQL Server
• Scheduling a Job in SQL Server

Posted by Andrew Gould on 02 August 2023

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Importing data into a SQL Server database isn't really that tricky: there's a straightforward wizard that you can follow to get information from Microsoft Excel, Access and even text files.  But what if you want to perform the same import of data on a regular basis?  Having to go through the wizard on a weekly, daily, or even hourly basis could become very tedious very quickly!

Fortunately, there's a way to set up a scheduled data import in SQL Server meaning that you don't have to go through the same process each time you want to import a set of data.  There are two main steps involved in this process:

• Use the SQL Server Import and Export Wizard to create a SQL Server Integration Services (SSIS) package .
• Schedule a job which executes the SSIS package according the schedule you want.

This blog explains the process using SQL Server 2008 R2 and an Excel 2010 spreadsheet.

If you need background information on some of the techniques mentioned in this blog, you might want to familiarise yourself with our SQL Tutorial blog series first (or book look at our other SQL training resources).

Our Example

For this blog we're going to import a set of data related to movies from an Excel spreadsheet into an existing SQL Server database.

Launching the Import Wizard

The first step in this process is launching the wizard that is used to import data.  To do this:

• In SQL Server Management Studio, locate your database in the Object Explorer pane at the left hand side of the screen.
• Right-click on the name of the database and choose: Tasks -> Import Data...

Choose this option to start the import wizard.

The next part of this blog series explains the steps to follow in the import wizard in order to create an SSIS package.

• SQL training
• SQL Server training
• Macros and Programming training

• SQL Server training
• Write for us!

How to import/export data to SQL Server using the SQL Server Import and Export Wizard

This article will explain the steps for importing and exporting data using the SQL Server Import and Export Wizard.

Depending on the options that are selected in the SQL Server Import and Export Wizard, some steps may be different than those described in this article.

For the purpose of this example, exporting data from the SQL Server database to an Excel file will be shown.

There are several ways to initiate the SQL Server Import and Export Wizard:

• Using the Start menu
• Using the Command prompt
• Using SQL Server Management Studio
• Using Visual Studio with SQL Server Data Tools

In the Start menu, type the word Import or Export and choose one of the offered:

Or, in the Start menu under the Microsoft SQL Server 2017 , choose one of the offered options:

• SQL Server 2017 Import and Export Data (32-bit)
• SQL Server 2017 Import and Export Data (64-bit)

Command prompt

In the Command prompt, type the following: C:\Program Files\Microsoft SQL Server\140\DTS\Binn\ DTSWizard.exe for the 64-bit version:

Or, type the following C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn DTSWizard.exe for the 32-bit version.

Or, go to above mentioned locations and, from there, run DTSWizard.exe:

SQL Server Management Studio (SSMS)

To start the SQL Server Import and Export Wizard using SSMS, go to Object Explorer , right click on a database for which want to export or import data; from the context menu under the Task sub-menu, choose the Import Data or Export Data option:

The latest version of SSMS can be downloaded from the Download SQL Server Management Studio (SSMS) page

SQL Server Data Tools (SSDT)

Another way to launch the SQL Server Import and Export Wizard is by using SQL Server Data Tools (SSDT) via Visual Studio.

SQL Server Data Tools (SSDT) can be downloaded from the Download SQL Server Data Tools (SSDT) page:

Under the Integration Services project:

Go to the Project menu and choose the SSIS Import and Export Wizard option:

Or in Solution Explorer , right click the SSIS Packages folder and, from the context menu, choose the SSIS Import and Export Wizard option:

The common warning message that appears when using the SQL Server Import and Export Wizard is:

The operation could not be completed.

“ADDITIONAL INFORMATION: The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. (System.Data)”

This happens because of the choice of the wrong version (32-bit or 64-bit) of the SQL Server Import and Export Wizard. For example, if exporting data from the 64-bit version of SQL Server and using the SQL Server Import and Export Wizard via SSMS which is 32-bit application and include 32-bit version of the SQL Server Import and Export Wizard and, because of that, the above warning message will appear. To resolve this, launch 64-bit version of the SQL Server Import and Export Wizard.

Bear in mind that SQL Server Data Tools (SSDT) is a 32-bit application, too.

Also, when the SQL Server Import and Export Wizard is used, make sure that you have adequate permissions, otherwise one of the following messages may appear:

More about necessary permissions in order to use the SQL Server Import and Export Wizard successfully can be found on this page

The SQL Server Import and Export Wizard uses SQL Server Integration Services (SSIS) to copy data. The SQL Server Import and Export Wizard creates an SSIS package in the memory while you set the options in the wizard. In the end, provide an option to save the SSIS package:

More about SSIS package can be found on the SQL Server Integration Services page.

After launching the SQL Server Import and Export Wizard Welcome page, the following will appear:

To prevent this page to appear next time when the SQL Server Import and Export Wizard is launched, check the Do not show this starting page again. checkbox.

To proceed, click the Next button, the Choose a Data Source page will be shown:

On this page, the info about data source and how to connect to the data source must be provided.

From the Data source combo box, select the data provider that will connect to the data source. The name of the data provider typically contains a name of the data source. For example, the data driver for connecting to SQL Server is SQL Server Native Client 11.0 or for connecting to the Excel files is Microsoft Excel etc.

The Data source combo box lists only the data providers that are installed on your machine. Also, if the 64-bit SQL Server Import and Export Wizard is used in the Data source combo box, won’t be listed installed 32-bit providers.

Pressing the F1 key from the keyboard while on any page of the SQL Server Import and Export Wizard will open the corresponding documentation for that page.

The same thing can be achieved by clicking the Help button on the SQL Server Import and Export Wizard pages:

Options that will be shown on the SQL Server Import and Export Wizard pages depend on the selected data source in the Data source combo box.

In our example, the SQL Server Native Client 11.0 provider will be used.

In the Server name combo box, select the SQL Server instance that contains the source data.

Note : If you are work with multiple servers or on a network, it is better to enter the name of the server instead to expand the combo box, because it may take a long time to list all available servers and it can cause the Not Responding state:

Under the Authentication part, choose how to connect to the data source, by using Windows or SQL Server authentication.

In the Database combo box, select a database from which want to export data:

When everything is set on the Choose a Data Source page, press the Next button to continue. The next page in the SQL Server Import and Export Wizard is the Choose a Destination page:

On this page, the information about where the data will be placed and how to connect to the destination must be provided.

To specify destination from the Destination combo box, select the provider that will import data to the destination.

A number of options that will be shown on this page depends of the selected provider in the Destination combo box.

For the purpose of this article, the Microsoft Excel provider will be selected in the Destination combo box for importing data to the Microsoft Excel file:

From the name of the provider, it can be determined which destination the provider is intended for. For example, the Microsoft Access (Microsoft Access Database Engine) provider is intended to import data to Microsoft Access, the .Net Farmworker Data Provider for Oracle is for importing the Oracle database, etc.

The Destination combo box lists only the data providers that are installed on your machine. Also, if the 64-bit SQL Server Import and Export Wizard is used in the Destination combo box won’t be listed installed 32-bit providers.

As soon as the provider is chosen in the Destination combo box, additional options for settings will appear.

In the Excel file path box, type the destination to the Excel file, for example:

• For the destination on the local machine: C:\Users\Zivko\Desktop\ExportData.xlsx
• For the destination on the network: \\Example\Excel\ExportData.xlsx

Or use the Browse button on the Choose a Destination page to locate the Excel file:

Using the Open dialog, other files rather than Excel can be selected, but when press the Next button on the Choose a Destination page, the following warning message will appear:

“File path contains invalid Excel file. Please provide file with .xls, .xlsx, .xlsm, or .xlsb extension.”

The SQL Server Import and Export Wizard does not support a password-protected Excel file . In case that password-protected Excel file is used, the similar warning message may appear:

“The operation could not be completed. External table is not in the expected format. (Microsoft Access Database Engine)”

Under the Excel version combo box, choose the version of Excel for the Excel file.

In some cases, an additional file must be installed in order to successfully connect to the Excel file , otherwise, the following warning message may appear:

“The operation could not be completed. The ‘Microsoft.ACE.OLEDB.16.0’ provider is not registered on the local machine. (System.Data)”

The next page in the SQL Server Import and Export Wizard is the Specify Table Copy or Query page:

On this page, two radio buttons exist:

Copy data from one or more tables or views

Write a query to specify the data to transfer.

The Copy data from one or more tables or views option is for copying all data from the existing tables or views in the source database.

The Write a query to specify the data to transfer option is for copying specific data from the source database by using a query.

To copy more than one database or database objects that are not tables and views, use the Copy Database Wizard instead of the SQL Server Import and Export Wizard.

Choose the Copy data from one or more tables or views or Write a query to specify the data to transfer radio button and click the Next button.

If the Copy data from one or more tables or views radio button is chosen, then, the next page in the SQL Server Import and Export Wizard will be the Select Source Tables and Views page:

In the Tables and views grid under the Source column, all available views and tables from the AdventureWorks2014 database will be listed.

Use the check box next to the table/view to copy data from source to destination:

The name of the copied view/table in the Destination column can be changed by clicking on the name of the view/table:

The name sets here will be used as the name of the worksheet in the Excel file:

By clicking the Edit Mappings button, the Column Mappings dialog will appear:

Here can be set additional options of how and which data will be exported.

At the beginning of the Column Mappings dialog, under Source, the name of the source query, view, or table is shown. In Destination, the name of the destination view or table is shown:

The Create destination table option, if the destination table does not exist, creates a new destination table (e.g. ProductTest).

When clicking the Edit SQL button, the Create Table SQL Statement dialog will appear:

This box automatically generates the CREATE TABLE statement that can be modified/customized, more about this can be found on the Create Table SQL Statement page.

The Create destination table radio button and Edit SQL button are disabled if the destination name already exists:

If the destination name exists the Delete rows in destination table and Append rows to destination table radio buttons are enabled:

If the Delete rows in destination table radio button is selected, this will clear all data from the existing table/files before importing the new data.

If the Append rows to destination table radio button is selected, this will put the new data to the data that already exist to the destination table/file.

The Drop and re-create destination table checkbox is only enabled when the package that the SQL Server Import and Export Wizard creates is saved and then the package runes again.

The Mappings section of the Column Mappings dialog shows the column of the data source and corresponding the columns in the destination:

The Source column lists all columns from the source column.

The Destination column lists the name of the mapped destination column. In this column, the column that you don’t want to import into destination file can be excluded by selecting the <ignore> item from the combo box:

The Type column shows the data type for the destination columns. The data type can be changed by selecting other type from the combo box:

In the Nullable column, it can be specified whether the destination column allowed a null value or not.

In the Size column, the size of the destination column can be specified:

In the Precision column, the precision of the numeric data for the destination column can be specified, here can be set the number of the digits.

In the Scale column, the number of the decimal place for the numeric destination column can be set:

To preview data that will be copied to the destination, under the Select Source Tables and Views page, click the Preview button. This will open the Preview Data dialog box:

The Preview Data dialog box displays up to 200 rows of data from the data source.

If the Write a query to specify the data to transfer radio button is chosen on the Specify Table Copy or Query page of the SQL Server Import and Export Wizard, then the Provide a Source Query dialog box will appear:

In this dialog, a query from the file can be written, pasted or loaded by clicking the Browse button, which selects source data to copy to the destination.

In the SQL statement editor, put the desired query and press the Parse button to check if the SQL statement is valid:

Now, when press the Next button, the Select Source Tables and Views page will appear; in the Tables and view grid the [Query] item that represents the query that is written in the Provide a Source Query page will be shown:

The next page of the SQL Server Import and Export Wizard is the Review Data Type Mapping page:

This page shows information about data type conversation that will be performed to make the source data compatible with the destination.

If a problem with data type conversation exists, the warning icon next to the data source in the Table list will appear and, in the Data type mapping list, the warning icons will appear next to the source columns which indicates that this conversation may cause a loss of data:

To view additional information about an individual column, double click on that column and the Column Conversion Details dialog will appear:

On this page, source and destination information will be shown as well as the data type of the source and destination column, conversion steps etc.

On the Review Data Type Mapping page, the data type cannot be changed, but it can be returned to the Select Source Tables and Views page by clicking the Back button. On the Select Source Tables and Views page, click the Edit Mappings button to open the Column Mappings dialog box and, from there, change the data type.

After the data source and destination are specified and configured, click the Next button. The SQL Server Import and Export Wizard shows the Save and Run Package page :

By default, the Run immediately option for the import and export data immediately is checked. To save the settings as the SSIS package, the Save SSIS Package check box needs to be checked .

If the Save SSIS Package check box is checked, additional page will be shown when clicking the Next button:

On the Save SSIS Package page, additional options for saving the SSIS package can be specified. More about these options can be found on the Save SSIS Package page.

The next page of the SQL Server Import and Export Wizard is the Complete the Wizard page:

On this page, all choices that are made in this wizard will be shown. This page will show the following information:

• Data source location
• Destination location
• The data that to be copied
• Whether will be the package run immediately or saved etc.

To finalize the exporting process, click the Finish button. The SQL Server Import and Export Wizard will show the Performing Operation page:

This page shows the state of the data has been successfully exported or some errors occurred during exporting data.

The Action column shows the steps of the operation.

The Status column shows if the step finished successfully or with an error.

The Message shows the informational or error messages for each step.

To get more information about the step click on the displayed item:

To view a report of the results, save the report, copy report or send the report vie e-mail, click the Report button and from the menu, choose a desired option:

To verify that the SQL Server Import and Export Wizard successfully has exported data to the Excel file, find and open that Excel file, in our case that is the SQL Data.xlsx file:

• Recent Posts

• How to connect to a remote MySQL server using SSL on Ubuntu - April 28, 2020
• How to install MySQL on Ubuntu - March 10, 2020
• Using SSH keys to connect to a remote MySQL Server - November 28, 2019

Related posts:

• How to import data from an Excel file to a SQL Server database
• How to export data from SQL Server to a Flat file
• Cómo poder importar los datos de un archivo de Excel a una base de datos de SQL Server
• Cómo importar/exportar datos a SQL Server utilizando el Asistente para importación y exportación de SQL Server
• How to copy tables from one database to another in SQL Server

• Documentation

Try For Free

• The Excel and SQL Server experts blog

SQL Server Import and Export Wizard Guide

Download the free 14-day trial for the SQL Spreads Excel Add-in if you want to easily let your non-technical users update and manage SQL Server data from Excel.

This guide explains how to use the SQL Server Import and Export Wizard to get data into a SQL Server database.

Table of contents

• 1. What is the SQL Server Import and Export Wizard?
• a. Some example scenarios
• a. Step (1): Start the Wizard
• b. Step (2): Pick the source of the data
• c. Step (3): Pick the destination for the data
• d. Step (4): Specify what you want to copy
• e. Step (5): Configure the copy operation
• f. Step (6): Copy the data

What is the SQL Server Import and Export Wizard?

SQL Server Import and Export Wizard is a simple way to copy data from a source to a destination.  It is most commonly used to get data into or out of a SQL Server database, but it caters to several scenarios.

Although like most Microsoft wizards you are guided through the process quite easily, there are a few key steps in the process that you need to be aware of.  There are also quite a few dialogs to go through in the wizard, some of which need more attention than others.

This guide describes the data sources and destinations that can be used and goes through the step-by-step process by way of an example.

If you are looking for a simple way of importing/exporting data specifically from Excel to SQL Server, try the SQL Spreads Excel add-in .  

What data sources and destinations can be used?

The SQL Server Import and Export Wizard has a number of options available when it comes to selecting the data source and destination.  The main options are:

• Enterprise Databases (eg Microsoft SQL, Oracle)
• Open-source database (eg PostgreSQL, MySQL)
• Microsoft Excel
• Microsoft Access
• Text files (eg flat files like csv)
• Any source for which an ODBC driver, a .Net Framework Data Provider, or OLE DB Provider is available.

The list of available data sources that you’ll see when you run the wizard includes only the providers installed on your computer.  For example, the screenshot below shows the data sources that I can choose from. As you can see, I don’t have MySQL installed, so the provider for the MySQL data source is not available for me.

Some example scenarios

The range of options for data sources and destinations means that there are quite a few theoretical scenarios for using the SQL Server Import and Export Wizard.  The following are some of the more common ones:

• Importing data from a csv (comma-separated values) file to a SQL Server table
• Importing data from an Excel spreadsheet to a SQL Server table
• Exporting data from a SQL Server table to an Excel spreadsheet
• Importing data from an open-source database (eg MySQL) to SQL Server
• Exporting data from SQL Server to an open-source database

The scenario for importing data from a csv file to SQL Server is particularly common, and we’ve described this process in a previous article .  We’ve also described a simple and often overlooked method of importing data from Excel to SQL Server in this article .

How to use the SQL Server Import and Export Wizard

The SQL Server Import and Export Wizard is installed when you install SQL Server.  It is commonly used from within SQL Management Studio.  It can, however, be downloaded and installed as a stand-alone utility.

The way you launch the application depends on your use case.  For example, if you need to import data from a csv file to a SQL Server table, and you’re not a SQL user, you may launch the stand-alone version.

NOTE : If you launch the wizard from SQL Server Management Studio (select a database, right-click and selection Tasks > Import Data) and try and import from Excel, you may see an error message along the lines of “The operation could not be completed. The ‘Microsoft.ACE.OLEDB.16.0’ provider is not registered on the local machine.” This message appears because SSMS is a 32-bit application and you have a 64-bit version of Excel.  In this case, you’ll need to use the stand-alone version of SQL Server Import and Export Wizard.

Whichever way you launch the application – as a stand-alone or from within SQL Management Studio – the process is the same.

The steps we’re going to work through are summarized below:

• Start the Wizard
• Pick the source of the data
• Pick the destination for the data
• Specify what you want to copy
• Configure the copy operation
• Copy the data

The example scenario we’re going to work through involves importing data from an Excel spreadsheet into a new SQL Server table.  The Excel spreadsheet is here if you want to follow along yourself.  It contains two sheets: one containing a list of students, and one containing a list of subjects.  For this example, we are only interested in importing the list of students into SQL Server.

Step (1): Start the Wizard

In our example, I am a user that doesn’t use SQL Server Management Studio, so I’m going to launch the SQL Server Import and Export Wizard from my Start menu.

When you launch the application, the Welcome page  is displayed, and you can go ahead and click ‘Next’.

Step (2): Pick the source of the data

Our source data is in Excel, so on the Choose a Data Source  page, we can select “Microsoft Excel”.

When we select a data source, we need to specify some more information about it.  In this case, we need to provide the location of the Excel file and the version.  We can also indicate that the first row has column headers which should be used as the column names in the SQL Server table.

Note : when you select a data source type, the connection settings that you need to provide will change.  For example, if your data source is a flat file (eg a csv file), you need to specify the file location and some details of the file format.

Step (3): Pick the destination for the data

On the Choose a Destination page, we now need to specify the destination for the data.  For our example, the destination is a SQL Server table, so from the destination drop down menu we need to select SQL Server Native Client (you could also select Microsoft OLE DB Driver for SQL Server).

When you select the SQL Server option, the Authentication and Database options are displayed.  In this example we’re going to select ‘Windows Authentication’ and we’re going to copy the data into a table in a new database by clicking ‘New’ in the database section.  If we want to copy the data to a table in an existing database, we can select from the Database drop down menu instead of creating a new database.

Click ‘Next’

Step (4): Specify what you want to copy

The Excel workbook that we are using for this example contains two sheets. We are only interested in the Students sheet for now, so we need to specify that only this will be copied.

First, on the Select Table Copy or Query page, we need to confirm the default option of ‘Copy data from one or more tables or views’.  This just means that we are copying all the data and not a subset of data.  This configuration option is more relevant when you are copying from a SQL database, and you want to write a specific SELECT statement, for example, to filter the data being copied.

On the Select Source Tables and Views page, we can do the following to further define how the copy is performed:

• choose the specific sheet(s) within the workbook that we want to copy
• (optional) re-name the table that will be created in SQL Server
• (optional) edit the column mappings

First, we need to tick the first sheet in the list, as that has the student data that we want to copy.  Note that we can copy as many spreadsheets as we like during this operation.  We’re also going to rename the table that will be created in SQL Server.  The default is for the table to be given the same name as the sheet name with a dollar sign appended to it; we’re going to remove the dollar sign.

Next we’re going to edit the column mappings by clicking the ‘Edit Mappings…’ button.

Step (5): Configure the copy operation

First we’re going to change the column names in the destination table so that they use lowercase and underscores.  Secondly, we’re going to change the data type for the ‘date_of_birth’ column from datetime to date.

Once done, click ‘Ok’ to close the dialog and then ‘Next’.

If any of the mappings that we specified may not succeed in the copy process the SQL Server Import and Export Wizard shows the Review Data Type Mapping page. This page will indicate the conversions that the wizard needs to perform to make the source data compatible with the destination.  In our example, there is a warning that the conversion from DateTime to Date for the ‘Date of Birth’ column may lead to data loss.  We don’t need to worry about this warning as it is simply implying that the time portion of the Date of Birth won’t be copied to the new column in the table – this is actually what we are aiming for.

Click the ‘Next’ button.

Step (6): Copy the data

The wizard now offers you the chance to save the Wizard configuration as an SSIS package in addition to the default option of running the copy immediately.

Note : The SQL Import and Export Wizard uses SSIS under the hood; in other words, each time you run the wizard, you are creating an SSIS package.  This is why you are given the option of saving it for future use.

We are just going to leave the default option ticked and then click the ‘Next’ button.

The wizard now displays a summary of the steps that we have configured.

Click ‘Finish’, and the wizard will now show the final screen, which is the result of the execution.  In our case, the execution was successful, and we can see that 1000 records were copied to the students table in the demo database in SQL Server.

As a final check, we can open SQL Server Management Studio and check that the data has been copied successfully to the students table in the new demo database.

SQL Import and Export Wizard offers a comprehensive range of options to copy data from a source to a destination.  In this example, we have shown how to use the wizard for a common use case: copying data from Excel to SQL Server.

If you are looking for a simple alternative to SQL Import and Export Wizard to import and export data from Excel to SQL Server, try SQL Spreads .

*This article was originally published on July 20, 2021 and was updated on May 6, 2022 to include additional information about the versions of the standalone utility.

Andy McDonald

Andy has worked 20+ years in the Engineering, Financial, and IT sectors with data analysis and presentation using tools such as SQL Server, Excel, Power Query and Power BI.

Writes for SQL Spreads about Excel and SQL Server and how to tie those two together.

Leave a Reply

Website URL

Comment Type your comment here...

No comments yet. Be the first!

Hi, I am the founder of SQL Spreads , the lightweight data management solution to update SQL Server data using Excel

In this blog we share the Excel and SQL Server knowledge that we have learnt during our work with hundreds of customers worldwide.

Hope you find it useful!

Best regards, Johannes Åkesson

Love Excel? Need to update SQL Server data?

Use excel to update your data in sql server.

Try the SQL Spreads data management solution to update and manage your SQL Server data from within Excel.

• Privacy Policy

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Import and export data from SQL Server and Azure SQL Database

• 13 contributors

You can use a variety of methods to import data to, and export data from, SQL Server and Azure SQL Database. These methods include Transact-SQL statements, command-line tools, and wizards.

You can also import and export data in a variety of data formats. These formats include flat files, Excel, major relational databases, and various cloud services.

Methods for importing and exporting data

Use transact-sql statements.

You can import data with the BULK INSERT or the OPENROWSET(BULK...) commands. Typically you run these commands in SQL Server Management Studio (SSMS). For more info, see Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) .

Use BCP from the command prompt

You can import and export data with the BCP command-line utility. For more info, see Import and Export Bulk Data by Using the bcp Utility .

Use the Import Flat File Wizard

If you don't need all the configuration options available in the Import and Export Wizard and other tools, you can import a text file into SQL Server by using the Import Flat File Wizard in SQL Server Management Studio (SSMS). For more info, see the following articles:

• Import Flat File to SQL Wizard
• What's new in SQL Server Management Studio 17.3

Use the SQL Server Import and Export Wizard

You can import data to, or export data from, a variety of sources and destinations with the SQL Server Import and Export Wizard. To use the wizard, you must have SQL Server Integration Services (SSIS) or SQL Server Data Tools (SSDT) installed. For more info, see Import and Export Data with the SQL Server Import and Export Wizard .

Design your own import or export

If you want to design a custom data import, you can use one of the following features or services:

• SQL Server Integration Services. For more info, see SQL Server Integration Services .
• Azure Data Factory. For more info, see Introduction to Azure Data Factory .

Data formats for import and export

Supported formats.

You can import data from, and export data to, flat files or a variety of other file formats, relational databases, and cloud services. To learn more about these options for specific tools, see the following topics

• For the SQL Server Import and Export Wizard, see Connect to Data Sources with the SQL Server Import and Export Wizard .
• For SQL Server Integration Services, see Integration Services (SSIS) Connections .
• For Azure Data Factory, see Azure Data Factory Connectors .

Commonly used data formats

There are special considerations and examples available for some commonly-used data formats. To learn more about these data formats, see the following topics:

• For Excel, see Import from Excel .
• For JSON, see Import JSON Documents .
• For XML, see Import and Export XML Documents .
• For Azure Blob Storage, see Import and Export from Azure Blob Storage .

If you're not sure where to begin with your import or export task, consider the SQL Server Import and Export Wizard. For a quick introduction, see Get started with this simple example of the Import and Export Wizard .

Submit and view feedback for

Additional resources

Import or link to data in an SQL Server database

You can link to or import data from an SQL Database, which is a high-performing managed database used for mission-critical applications. For more information, see SQL Server 2016 .

When you link to data, Access creates a two-way connection that synchronizes changes to data in Access and the SQL Database.

When you import data, Access creates a one-time, copy of the data, and so changes to data in either Access or the SQL Database are not synchronized.

Before you begin

Want things to go smoother? Then make the following preparations before you link or import:

Locate the SQL Server database server name, identify necessary connection information, and choose an authentication method (Windows or SQL Server). For more information on the methods of authentication, see Connect to Server (Database Engine) and Securing your database .

Identify the tables or views that you want to link to or import, and uniquely-valued fields for linked tables. You can link to or import more than one table or view in a single operation.

Consider the number of columns in each table or view. Access does not support more than 255 fields in a table, so Access links or imports only the first 255 columns. As a workaround, you can create a view in the SQL Server Database to access the columns beyond the limit.

Determine the total amount of data being imported. The maximum size of an Access database is two gigabytes, minus the space needed for system objects. If the SQL Server database contains large tables, you might not be able to import them all into a single Access database. In this case, consider linking to the data instead of importing.

Secure your Access database and the connection information it contains by using a trusted location and an Access database password. This is especially important if you choose to save the SQL Server password in Access.

Plan for making additional relationships. Access does not automatically create relationships between related tables at the end of an import operation. You can manually create the relationships between new and existing tables by using the Relationships window. For more information, see What is the Relationships window? and Create, edit or delete a relationship .

Stage 1: Get started

Select External Data > New Data Source > From Database > From SQL Server .

In the Get External Data – ODBC Database dialog box, do one of the following:

To import data, select Import the source data into a new table in the current database .

To link to data, select Link the data source by creating a linked table .

Select OK .

Stage 2: Create or reuse a DSN file

You can create a DSN file or reuse an existing one. Use a DSN file when you want to rely on the same connection information for different link and import operations or to share with a different application that also uses DSN files. You can create a DSN file directly by using the Data Connection Manager. For more information, see Administer ODBC data sources .

Although you can still use prior versions of the SQL ODBC driver, we recommend using version 13.1, which has many improvements, and supports new SQL Server 2016 features. For more information, see Microsoft ODBC Driver for SQL Server on Windows .

Do one of the following:

If the DSN file you want to use already exists, select it from the list.

Depending on which authentication method you entered in the connection information, you may need to enter a password again.

To create a new DSN file:

Select New .

Select ODBC Driver 13 for SQL Server , and then select Next .

Enter a name for the DSN file, or click Browse to create the file in a different location.

Click Next to review the summary information, and then click Finish .

Stage 3: Use the Create a New Data Source to SQL Server wizard

In the Create a New Data Source to SQL Server wizard, do the following:

On page one, enter identification information:

In the Description box, optionally enter documentary information about the DSN file.

In the Server box, enter the name of the SQL Server. Do not click the down arrow.

On page two, select one of the following authentication methods:

With Integrated Windows authentication     Connect through a Windows user account. Optionally, enter a Service Principle name ( SPN ). For more information, see Service Principal Names (SPNs) in Client Connections (ODBC) .

With SQL Server authentication…    Connect with credentials that have been set up in the database by entering the login ID and password .

On pages three and four, select various options to customize your connection. For more information about these options, see Microsoft ODBC Driver for SQL Server .

A screen appears to confirm your settings. Select Test Data Source to confirm your connection.

You may need to login to the database. In the SQL Server Login dialog box, enter the login ID and password. To change additional settings, select Options .

Stage 4: Select tables and views to link to or import

In the Link Tables or Import Objects dialog box, under Tables , select each table or view that you want to link or import, and then click OK .

In a link operation, decide whether to select Save Password .

Security     Selecting this option eliminates the need to enter credentials each time you open Access and access the data. But, this stores an unencrypted password in the Access database, which means people who can access the source contents can see the user name and password. If you select this option, we strongly recommend storing the Access database in a trusted location and creating an Access database password. For more information, see Decide whether to trust a database and Encrypt a database by using a database password .

Note     If you decide not to save the password, but then change your mind, you need to delete and re-create the linked table, and then select Save Password .

Stage 5: Create specifications and tasks (Import only)

In the Get External Data - ODBC Database dialog box, you can save the import steps as a specification and create an Outlook task to automate the import operation on a regular basis. For more information, see Save the details of an import or export operation as a specification .

When a link or import operation completes, the tables appear in the Navigation Pane with the same name as the SQL Server table or view combined with the owner name. For example, if the SQL name is dbo.Product, the Access name is dbo_Product. If that name is already in use, Access appends "1" to the new table name — for example, dbo_Product1. If dbo_Product1 is also already in use, Access will create dbo_Product2, and so on. But you can rename the tables to something more meaningful.

In an import operation, Access never overwrites a table in the database. Although you cannot directly append SQL Server data to an existing table, you can create an append query to append data after you have imported data from similar tables.

In a link operation, if columns are read-only in an SQL Server table, they are also read-only in Access.

Tip     To see the connection string, hover over the table in the Access navigation pane.

Update the linked table design

You can’t add, delete, or modify columns or change data types in a linked table. If you want to make design changes, do it in the SQL Server database. To see the design changes in Access, update the linked tables:

Select External Data > Linked Table Manager .

Select each linked table you want to update, select OK , and then select Close .

Compare data types

Access data types are differently named from SQL Server data types. For example, a SQL Server column of the bit data type is imported or linked into Access with the Yes/No data type. For more information, see Comparing Access and SQL Server data types .

You can work with data stored in SQL Server either by linking to it or importing the data into an Access database. Linking is a better option if you share the data with others because the data is stored in a centralized location and you can view the most current data, add or edit the data, and run queries or reports in Access.

Step 1: Preparation for linking

Locate the SQL Server database that you want to link to. If necessary, contact the database administrator for connection information.

Identify the tables and views you’ll be linking to in the SQL database. You can link to multiple objects at a time.

Review the source data for the following considerations:

Access supports up to 255 fields (columns) in a table, so the linked table will include only the first 255 fields of the object you link to.

The columns that are read-only in a SQL Server table will also be read-only in Access.

To create the linked table in a new database: Click File > New > Blank desktop database . To create the linked tables in an existing Access database, make sure that you have the necessary permissions to add data to the database.

Note:  A linked table created in an existing Access database, gets the same name as in the source object. So, if you already have another table with the same name, the new linked table name has a “1” added to it — for example, Contacts1. (If Contacts1 is also already in use, Access will create Contacts2, and so on.)

Step 2: Linking to data

When linking to a table or view in a SQL Server database, Access creates a new table (known as a linked table) that reflects the structure and contents of the source table. You can change the data either in SQL Server, or in Datasheet view or Form view from Access and the changes are reflected in both SQL and Access. Any structural changes to linked tables like removing or changing columns, have to be made from the SQL Server and not Access.

Open the destination Access database.

On the External Data tab, click ODBC Database .

Click Link to the data source by creating a linked table > OK and follow the steps in the wizard.In the Select Data Source box, if the .dsn file you want to use already exists, click the file in the list.

To create a new .dsn file:

In the Select Data Source box, click New > SQL Server > Next .

Type a name for the .dsn file, or click Browse .

Note:  You need write permissions to the folder to save the .dsn file.

Click Next to review the summary information, and click Finish .

Follow the steps in the Create a New Data Source to SQL Server Wizard.

Click OK and under Tables , click each table or view that you want to link to, and then click OK .

If you see the Select Unique Record Identifier , it means that Access was unable to determine which field or fields uniquely identify each row of the source data. Just select the field or combination of fields that is unique for each row, and if you are not sure, check with the SQL Server database administrator.

When the linking operation is complete, you can see the new linked table or tables in the Navigation Pane.

Apply the latest SQL Server object structure

When you open either a linked table or the source object, you see the latest data. However, if any structural changes are made to a SQL Server object, you’ll need to update the linked table(s) to see those changes.

Right-click the table in the Navigation Pane, and then click Linked Table Manager on the shortcut menu.

Select the check box next to each linked table that you want to update, or click Select All to select all of the linked tables.

Click OK > Close .

Note:  Since Access data types differ from SQL Server data types, Access links to the most appropriate data type for each column. You can only review not change the assigned data types in Access.

For more information, see ways to share an Access desktop database.

Top of Page

If your department or workgroup uses Microsoft SQL Server to store data, you might have to work with some SQL Server data in Access.

You can bring data from SQL Server objects (tables or views) into Access in either of two ways — by importing, or by linking. The difference between the two processes is as follows:

When you import the data, Access creates a copy of the SQL Server data and any later changes that are made to the data in your Access database are not reflected in the SQL Server database. Likewise, any later changes made in the SQL Server table or view are not reflected in Access.

When you link to the SQL Server data, you are connecting directly to the source data so any later changes that are made to data in Access are reflected in the SQL Server, and vice versa.

This article describes how to either import or link to SQL Server data.

Decide whether to import or to link

Situations when importing is suitable.

Typically, you import SQL Server data to an Access database for these reasons:

To permanently move SQL Server data to an Access database because you no longer need the data in the SQL Server database. After you import the data into Access, you can delete the data from the SQL Server database.

Your department or workgroup uses Access, but you are occasionally pointed to a SQL Server database for additional data that must be merged into one of your Access databases.

Since importing SQL Server data creates a copy of the data in your Access database, during the import process, you specify the tables or views that you want copied.

Situations when linking is suitable

Typically, you link to SQL Server data for these reasons:

To connect directly to the source data to be able to view and edit the latest information both in the SQL Server database and in your Access database.

The SQL Server database contains many large tables, and you are not be able to import them all into a single .accdb file. The maximum size of an Access database is 2 gigabytes, minus the space needed for system objects.

You want to run queries and generate reports based on data from the SQL Server without making a copy of the data, consider linking to the SQL Server.

Your department or workgroup uses Access for reporting and querying and uses SQL Server for data storage. Individual teams can create SQL Server tables and views for centralized storage, but often this data must be brought into desktop programs for aggregation and reporting. Linking is the appropriate choice, because it allows users of both the SQL Server database and the Access database to add and update data, and to always view and work with the latest data.

You are an Access user who recently started using SQL Server. You migrated several of your databases to SQL Server, and most of the tables in these databases are linked tables. From now on, instead of creating Access tables, you will create tables and views in SQL Server and then link to them from your Access databases.

You want to continue storing your data in SQL Server, but you also want to work with the most recent data inside Access in order to run queries and print reports that you designed in Access.

Import data from SQL Server

Prepare to import.

During the import operation, Access creates a table and then copies the data from the SQL Server database into that table. At the end of the import operation, you can choose to save the details of the import operation as a specification.

Note:  An import specification helps you to repeat the import operation in the future without having to step through the Import Wizard each time.

Locate the SQL Server database that contains the data that you want import. Contact the administrator of the database for connection information.

Identify the tables or views that you want to import. You can import multiple objects in a single import operation.

Review the source data and keep the following considerations in mind:

Access does not support more than 255 fields in a table, so Access imports only the first 255 columns.

The maximum size of an Access database is 2 gigabytes, minus the space needed for system objects. If the SQL Server database contains many large tables, you might not be able to import them all into a single .accdb file. In this case, you might want to consider linking the data to your Access database instead.

Access does not automatically create relationships between related tables at the end of an import operation. You must manually create the relationships between the various new and existing tables by using the options in the Relationships window. To display the Relationships window:

Click Database Tools > Relationships .

Identify the Access database into which you want to import the SQL Server data.

Ensure that you have the necessary permissions to add data to the Access database. If you don't want to store the data in any of your existing databases, create a new blank database.

Review the tables, if any exist, in the Access database.

The import operation creates a table with the same name as the SQL Server object. If that name is already in use, Access appends "1" to the new table name — for example, Contacts1. (If Contacts1 is also already in use, Access will create Contacts2, and so on.)

Note:  Access never overwrites a table in the database as part of an import operation, and you cannot append SQL Server data to an existing table.

Import the data

Open the destination database.

On the External Data tab, in the Import & Link group, click ODBC Database .

Click Import the source data into a new table in the current database , and then click OK .

In the Select Data Source dialog box, if the .dsn file that you want to use already exists, click the file in the list.

I need to create a new .dsn file

Note:  The steps in this procedure might vary slightly for you, depending on the software that is installed on your computer.

Click New to create a new data source name (DSN).

The Create New Data Source Wizard starts.

In the wizard, select SQL Server in the list of drivers, and then click Next .

Type a name for the .dsn file, or click Browse to save the file to a different location.

Note:  You must have write permissions to the folder to save the .dsn file.

Click Next , review the summary information, and then click Finish to complete the wizard.

Create a New Data Source to SQL Server dialog box appears.

Type a description of the data source in the Description box. This step is optional.

Under Which SQL Server do you want to connect to , in the Server box, type or select the name of the SQL Server to which you want to connect, and then click Next to continue.

You might require information from the SQL Server database administrator, such as whether to use Microsoft Windows NT authentication or SQL Server authentication. Click Next to continue.

If you want to connect to a specific database, ensure that the Change the default database to check box is selected. Then select the database that you want to work with, and then click Next .

Click Finish .

Review the summary information and click Test Data Source .

Review the test results, and then click OK to close the dialog box.

If the test was successful, click OK again, or click Cancel to change your settings.

Click OK to close the Select Data Source dialog box.

Access displays the Import Objects dialog box.

Under Tables , click each table or view that you want to import, and then click OK .

If the Select Unique Record Identifier dialog box appears, Access was unable to determine which field or fields uniquely identify each row of a particular object. In this case, select the field or combination of fields that is unique for each row, and then click OK . If you are not sure, check with the SQL Server database administrator.

Access imports the data. If you plan to repeat the import operation later, you can save the import steps as an import specification and easily rerun the same import steps later. You require Microsoft Office Outlook installed on your computer to create a task.

Click Close under Save Import Steps in the Get External Data - ODBC Database dialog box. Access finishes the import operation and displays the new table or tables in the Navigation Pane.

If you want to save the import as a task for reuse, continue to the next section.

Link to SQL Server data

Since data is stored in tables, when you link to a table or view in a SQL Server database, Access creates a new table (often known as a linked table) that reflects the structure and contents of the source object. You can change data either in SQL Server, or in Datasheet view or Form view from Access. The changes that you make to data in one location are reflected in the other. However, if you want to make structural changes, such as removing or changing a column, you must do so from the SQL Server database, or from an Access project that is connected to that database. You cannot add, delete, or change the fields in a linked table while you are working in Access.

Prepare to link

Locate the SQL Server database that has the data to which you want to link. Contact the database administrator for connection information.

Identify the tables and views to which you want to link. You can link to multiple objects in a single linking operation.

Access does not support more than 255 fields in a table, so the linked table will include only the first 255 fields of the object you link to.

The columns that are read-only in a SQL Server object will continue to be read-only in Access.

You will not be able to add, delete, or modify columns in the linked table in Access.

Identify the Access database in which you want to create the linked tables. Ensure that you have the necessary permissions to add data to the database. If you don't want to store the data in any of your existing databases, create a new, blank database by clicking the File tab, and then on the New tab, clicking Blank Database .

Review the tables in the Access database. When you link to a SQL Server table or view, Access creates a linked table with the same name as the source object. If that name is already in use, Access will append "1" to the new linked table name — for example, Contacts1. (If Contacts1 is also already in use, Access will create Contacts2, and so on.)

To link to the data, open the destination database.

Click Link to the data source by creating a linked table , and then click OK .

In the Select Data Source dialog box, click the .dsn file that you want to use, or click New to create a new data source name (DSN).

In the Select Data Source dialog box, if the .dsn file you want to use already exists, click the file in the list.

Click Next , review the summary information, and then click Finish to complete the Create New Data Source Wizard.

The Create a New Data Source to SQL Server Wizard starts.

In the wizard, type a description of the data source in the Description box. This step is optional.

Under Which SQL Server do you want to connect to , in the Server box, type or select the name of the SQL Server computer to which you want to connect, and then click Next to continue.

On this page of the wizard, you might need to get information from the SQL Server database administrator, such as whether to use Windows NT authentication or SQL Server authentication. Click Next to continue.

On the next page of the wizard, you might need to get more information from the SQL Server database administrator. If you want to connect to a specific database, ensure that the Change the default database to check box is selected, select the SQL Server database that you want to work with, and then click Next .

Click Finish . Review the summary information, and then click Test Data Source .

Review the test results, and then click OK to close the SQL Server ODBC Data Source Test dialog box.

If the test was successful, click OK again to complete the wizard, or click Cancel to return to the wizard and change your settings.

Access displays the Link Tables dialog box.

Under Tables , click each table or view that you want to link to, and then click OK .

If the Select Unique Record Identifier dialog box appears, Access was unable to determine which field or fields uniquely identify each row of the source data. In this case, select the field or combination of fields that is unique for each row, and then click OK . If you are not sure, check with the SQL Server database administrator.

Access finishes the linking operation and displays the new linked table or tables in the Navigation Pane.

Important:  Each time you open either a linked table or the source object, you see the latest data displayed in it. However, structural changes made to a SQL Server object are not automatically reflected in a linked table.

Update a linked table by applying the latest SQL Server object structure

If the update is successful, Access displays a message to that effect. Otherwise, Access displays an error message.

Click Close to close the Linked Table Manager.

What else should I know?

For information on how to save the details of your import into a specification that you can reuse later, see the article Save the details of an import or export operation as a specification .

For information on how to run saved import specifications, see the article Run a saved import or export operation .

For information on how to schedule specifications to run at specific times, see the article Schedule an import or export operation .

For information on how to change a specification name, delete specifications, or update the names of source files in specifications, see the article Manage Data Tasks .

How Access sees SQL Server data types

Because Access data types differ from SQL Server data types, Access must determine the most appropriate Access data type to use for each column of each SQL Server table or view that you import or link to. For example, a SQL Server column of the data type bit is imported or linked into Access with the data type Yes/No . Another example, a SQL Server column of the data type nvarchar(255) (or smaller) is imported or linked with the data type Text , but a column of the data type nvarchar(256) (or larger) is imported as an Access Memo field. After completing an import or linking operation, you can open the table in Design view and confirm which data types Access assigned to its fields. You can change data types of fields in imported tables. However, you cannot change data types of fields in linked tables, except by changing them in the SQL Server database itself or in an Access project that is connected to that database.

The following table lists the main SQL Server data types. The second and third columns show how Access interprets each type.

Need more help?

Want more options.

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

Different Options for Importing Data into SQL Server

By: Greg Robidoux   |   Comments (31)   |   Related: 1 | 2 | More > Import and Export

About the author

Comments For This Article

Related articles.

How to Import Data into SQL Tables

• Tomi Mester
• July 10, 2022

Following the previous article about creating data tables in SQL , now we want to load data into our freshly created SQL table. In this article, I’ll show you three different import methods:

• When you want to load the data line by line.
• When you want to insert the data from a .csv file.
• When you add rows to your new SQL table that are the results of another SQL query.

Note: This is going to be a practical tutorial, so I encourage you to do the coding part with me.

Note 2: If you are new here, let’s start with these SQL articles first:

• How to install Python, SQL, R, and Bash (for non-devs)
• How to install SQL Workbench for PostgreSQL
• SQL for Data Analysis – Tutorial for Beginners – ep 1
• How to create a table in SQL

How to Become a Data Scientist (free 50-minute video course by Tomi Mester)

Just subscribe to the Data36 Newsletter here (it’s free)!

Method #1: Load the Data Line by Line (INSERT INTO)

When we have only a few lines of data, the easiest way is to add them manually. We can do this by using the INSERT SQL statement:

Let’s get back to our test_results table that we created in the previous tutorial .

Currently, it’s an empty table. Let’s change that — and add a line to it using INSERT :

Excellent — that inserted a new row into our SQL table.

But let’s see the result and query our table!

SELECT * FROM test_results;

Oh, yeah! Walt’s test results are in the SQL table, indeed!

While this is a very manual process, you can speed it up if you INSERT the rest of the students with one bigger SQL statement:

Query the table once again to check out the results: SELECT * FROM test_results;

Now, we have 5 students’ data loaded into this sweet SQL table. That was easy as pie, right?

Now I want you to spend a few more seconds reviewing the syntax:

• INSERT INTO is the SQL keyword.
• test_results is the name of the table that we want to put the data into.
• VALUES is another SQL keyword.
• Then the actual data rows come one by one – each of them between parentheses ( () ) and separated by commas ( , ).
• The field values are also separated by commas ( , ).
• Watch out for the data points which data types are TEXT or DATE — these data points have to go between apostrophes ( ' ) when you write your SQL query!
• And never forget the semicolon ( ; ) at the end of your SQL statement!

If you are more of the visual type, here’s your cheat sheet:

Commit your changes!

As we have discussed in the previous article, if you do changes in your database with an SQL manager tool (like pgadmin4  or SQL Workbench ), you have to COMMIT them. Always! What does it mean? Learn more here . But for now, let’s just run this one extra line in your SQL manager:

Note: If you turned auto-commit on or if you are in the command line and not in an SQL query tool, then you can skip this commit step.

SQL TRUNCATE: empty your table without deleting the table

You have already learned about  the DROP TABLE SQL statement that deletes your SQL table. But very often you don’t want to delete your table (because you want to keep its structure), only clear that data. You can do this by using the TRUNCATE TABLE statement.

Type this: TRUNCATE TABLE test_results; This will delete all the rows that we have inserted in the table before, but it will keep the table itself.

Don’t forget that you have to commit your changes!

Note: more about emptying an SQL table here: SQL TRUNCATE TABLE and DROP TABLE tutorial .

Okay, if everything is set, let’s see the…

Method #2: insert a .csv file into an SQL table (COPY)

To be honest, this is a more common scenario than the first method I showed. As a data analyst, you quite regularly get raw data sets in file formats, like .xlsx or .csv or .txt . You can insert these data files using the COPY statement.

The general format of the statement looks like this:

COPY table_name FROM '/path/step/file_name' DELIMITER ' ';

Let me break it down for you:

• COPY is the SQL keyword that specifies that you’ll insert data from a file into an SQL table.
• table_name is the name of the table that you want to put the data into . (This is a bit counter-intuitive in the syntax… But we know that SQL is not the most “coder-friendly” tool syntax-wise. So just get over it and simply learn this way.)
• FROM is another SQL keyword after that you’ll…
• …specify the name and the location of the file that you want to COPY the data from . This goes between apostrophes.
• And eventually, you have to specify the field separator in your original file by typing DELIMITER and the field separator itself between apostrophes. So in this case ' ' means that the delimiter would be a space.

Example for COPY (insert .csv data into SQL)

Let’s go through the whole process with an example.

Note: in this example, I’ll help you to create a dummy .csv file. If you have your .csv file, you can just skip STEP #1, #2, and #3.

STEP 1) First, you have to open your Terminal window and connect to your data server. (Note: At this point assume you know how to do it – if not: this way please .)

STEP 2) Then type this (just copy-paste from here) into the command line:

This will create a .csv file called test_results.csv . (In real-life cases you will get this .csv file from someone at your company.)

STEP 3) Double-check your new file: cat test_results.csv . And find out the exact location of it by typing pwd .

STEP 4) Then you have to log in to PostgreSQL (still in your Terminal window): psql -U [your_sql_username] -d postgres (For me it’s psql -U dataguy -d postgres )

STEP 5) Then type the COPY statement we have just discussed above: \COPY test_results FROM '/home/dataguy/test_results.csv' DELIMITER ','; And boom, the data is inserted from our freshly created .csv file into our SQL table.

You can even query it from your SQL manager tool to double-check it:

The Junior Data Scientist's First Month

A 100% practical online course. A 6-week simulation of being a junior data scientist at a true-to-life startup.

“Solving real problems, getting real experience – just like in a real data science job.”

A few comments on the .csv data-load method

• I typed \COPY and not just COPY because my SQL user doesn’t have SUPERUSER privileges, so technically I could not use the COPY command (this is an SQL thing). Typing \COPY instead is the simplest workaround — but the best solution would be to give yourself SUPERUSER privileges then use the original COPY command. (In this video  starting at 2:55 I show how to give SUPERUSER privileges to your SQL user. If you are here from one of my online courses , probably we have already fixed this issue in the course.)
• Why we didn’t do the COPY command in our SQL manager tool? Same reason: if you don’t have SUPERUSER privileges, you can’t run the COPY command from an SQL manager tool — only from the command line. If you follow the video that I linked in the previous point, you will be able to run the same COPY statement from pgadmin or SQL Workbench.

• And finally: if you are uncomfortable with these command-line steps, read the first few articles from my Command Line for Data Analysts  article series.

And boom, the data is inserted from a .csv file into our SQL table. Run this query from your SQL manager:

Method #3: Insert the output of another SQL query into your SQL table

Do you want to store the output of your SQL query? Not a problem… Maybe you want to save your daily KPIs that are calculated from SQL tables — or you want to have the cleaned version of a data set next to the original. In SQL, you can do that easily.

Say we want to create a table where we want to store only the names from our test_results table. (This is a dummy example but it’ll do the job for now.)

Step 1) Create this new SQL table  :

Step 2) Use the INSERT INTO statement (that we learned in the “Method #1” section, at the beginning of this article), only instead of typing the values manually, put a SELECT statement at the end of the query.

Something like this:

Done! The subquery between parentheses will run first — then its output will be inserted automatically into the recently created student_names table.

Check the result: SELECT * FROM student_names;

You can even combine this method with SQL functions . For instance, you can calculate the average of the test results and then save that info into a new table. Something like this:

This new SQL table will store only one value: the average test result… but if we also had math test results, biology test results, and physics test results in other SQL tables, this test_averages table would be the perfect place to collect the different averages.

This was the third – slightly more advanced – way to insert your data into an SQL table. Now go ahead and test these methods on your own data set!

In this article we learned three methods to load data into SQL tables:

• When you want to INSERT your data manually. ( INSERT INTO ___ VALUES (____); )
• When you want to COPY your data from a file. ( COPY ____ FROM '_____' DELIMITER ' '; )
• When you want to store the output of another SQL query. ( INSERT INTO ____ (SELECT ____); )

Now, you know how to create new tables in SQL  and how to load data into them!

• If you want to learn more about how to become a data scientist, take my 50-minute video course: How to Become a Data Scientist.  (It’s free!)
• Also check out my 6-week online course: The Junior Data Scientist’s First Month video course.

Cheers, Tomi Mester

privacy policy

Data36.com by Tomi Mester | © all rights reserved This website is operated by Adattenger Kft.

Import Data to a MySQL Database

• Export Data
• Create MySQL Website

MySQL Workbench provides an easy way to import data from an external source.

Importing data via MySQL Workbench is a very similar process to exporting data . You can import a whole database. You can import just some tables and/or their data. Or you can import just the data.

Whether you're importing a whole database or just some data, you can do it all via the export/import wizard.

Preparation

If you're only importing data, you will need to make sure that the database and tables already exist.

If you're importing the database structure (i.e. the script creates the database and/or its tables), you will want to make sure you're not about to overwrite something that you shouldn't. The export script created with the export wizard will drop all tables and create them again, before inserting the data.

Because we just exported our database to an SQL file, we will use that file to create a new database and all its data.

Before we import our database, let's remove the old one first. So this will be like starting from scratch — we are restoring a database to a MySQL server that doesn't currently have that database. Once we've run the file, we will be able to verify that the database and its tables have been created and all data has been inserted.

So go ahead and run the following command:

Refresh the SCHEMAS tab and you should see that your database has disappeared.

Import the Database and/or Data

Use these steps whether you're importing a whole database, a table, or just the data.

When we exported our database to an SQL file, we checked the box that asked Include Create Schema . Because of this, the script will be able to create the database — no need for us to create that first.

Here are the steps involved in importing the whole database and its data. The same steps can be used if you only want to import some data or just the database structure without any data. The only difference is what you choose at step 3.

Start the Import

Ensure that the MANAGEMENT tab is selected in the left menu.

Click on the Data Export link.

Configure & Run the Import

The Data Import screen will appear.

Select Import from Self–Contained File and navigate to (or enter) the file to import.

In this case, because my script will create the database, I can leave Default Target Schema blank. Also, because I'm importing both the structure and data, I select Dump Structure and Data .

If you only need to import data, select Dump Data Only . If you only want to import the database structure (without data), select Dump Structure Only .

Once done, click Start Import .

The database will now be imported. Once completed, the Import Progress screen should read Import Completed .

Verify that the Database and/or Data has been Imported

If you imported data, you should query the table/s that the data was inserted into to check that the data has imported as expected.

If you imported the database structure, refresh the SCHEMAS tab and you should see the database listed. Navigate through the nodes to verify that the tables exist.

You can also run the following commands:

Display a List of Databases on the Server

Display the tables on the default database.

Here, we set the default database to be FruitShop by using USE FruitShop . We then ask MySQL to display all tables for the default database (by using SHOW TABLES ):

Display Table Structure

You could also find out the structure of a given table by using the following command (simply replace the table name with the table you need the structure of):