How to Import Data to SQL Server Tables?
In today's interconnected world, the need to exchange data across the boundaries of various information and data storage systems is increasing. SQL Server administrators and users are faced with constant necessity to transfer information into database tables from the rich diversity of file sources. Commonly, data exchange among different IT infrastructures is carried out through neutral file formats, like CSV, TXT or XML, but very often there are situations where the required information is stored in Excel, Access or even HTML documents. In this case a special solution is needed that is capable of importing information directly from the data file to SQL Server database without distortion of the information and time wasting.
Working with SQL Server Import Tool
In this article we would like to review EMS Data Import for SQL Server - a powerful and flexible database utility intended for importing data from MS Excel 97-2007, MS Access, DBF, XML, TXT, CSV, MS Word 2007, ODF and HTML files to SQL Server tables. The program has a user-friendly wizard interface and allows you to import data to SQL Server in a few simple steps. In addition to the wizard application which allows you to set all import options visually, EMS Data Import for SQL Server includes a command-line utility to import data in one-touch by performing the import data on the schedule basis according to the predefined settings.
Suppose we need to import data concerning our resellers, accounts and promotional campaigns into certain SQL Server database tables. For the test purpose we have prepared several data files in various data formats, containing information for the import: Resellers.xls, NewAccounts.mdb and NewPromotions.csv. Data from these files must be imported to the corresponding tables of SQL Server databases named Globals and Promotions.
At the first step of EMS Data Import for SQL Server wizard we should specify necessary settings to establish connection to the target SQL Server database. We choose the connection and authentication type, enter a remote SQL Server hostname and type in login credentials, if necessary.
On the next step we define one or more source data files and the corresponding tables from the database. First we need to select the file type and its location, and then select the SQL Server table the data will be imported to. Note that on this step we can add an unlimited number of source-destination pairs, source files can vary in types and target tables may be located in different SQL Server databases (Globals and Promotions in the our case). Let's pass on to the next step.
This step of the wizard allows us to set correspondence between columns of the source file and fields of the target SQL Server table according to the source data format. This correspondence and some other features are individual for each source file and can be established manually or automatically if the source file and the destination SQL Server table have the same order of columns or rows.
The next step of EMS Data Import for SQL Server wizard provides a number of options for setting base formats for each source data file. With the help of these formats Data Import can perform the conversion to SQL Server equivalents correctly if the source file contains data in a non-standard format. We do not have such a problem with our files and no additional changes are required. We pass on to the next step.
This step allows us to set formats for each imported field and the source file separately, in case additional formatting is required. For example, we can set the initial value of the autoincrement field, set a constant value for the field, enter the text that should be replaced during data import into the selected field and more. For this small demonstration scenario we will not change anything and proceed to the next step.
On this step we'll be setting various import processing modes for each source file. The chosen strategy of rewriting existing SQL Server database records determines the appropriate method of data replacement. Since our database is empty, we select the default mode - Insert All. As for the import speed, we'd recommend using the Native Mode in most cases. In this mode all search, insert and delete operations are performed on the SQL Server side. In the Universal Mode these operations are performed on the client side, which may result in a lower performance. In the Bulk Insert mode T-SQL BULK INSERT command is used to insert a group of records in the table. This mode accelerates the speed of records insertion considerably but we do not have a big amount of data and will be quite satisfied with the Single Commands mode. We pass on to the next step.
This step of the wizard allows us to select the fields of the table to be used as key fields for the import process. This field(s) will only serve for identification and will not be imported. Since we need to simply insert all records (we have chosen Insert All mode on the previous step), we could skip this. Let's move on to the next step.
Using this step we can set final import options such as transaction block size, number of records to be imported and the path to the file where the current script of SQL Server data import process will be saved. We will skip that and continue.
This step of EMS Data Import for SQL Server wizard allows us to define scripts to be executed for each database before and after the import process. We can also save and load Before Import and After Import scripts using the corresponding Save Script... and Load Script... buttons. One last step to pass!
This is the last step of the wizard. All necessary import parameters have been set and all we need is to launch the import process. Import has been completed successfully as reflected in the execution log. Well done. We have just imported information about resellers, its accounts and promotional campaigns into the corresponding SQL Server database tables using EMS Data Import utility.
More Useful SQL Server Tools
EMS Data Import for SQL Server is a useful tool that is capable to provide all the necessary functions of SQL Server data importing. It can be used independently or with EMS SQL Management Studio for SQL Server - the database toolset for a wide range of typical SQL Server DBA's everyday operations.