Summary of four ways to bulk import data in a SQL Server database _mssql

In the implementation of software projects, data import has always been a headache for project personnel. In fact, many methods of batch import data are integrated into SQL Server. Some project implementation consultant headaches, in the eyes of our database administrator, is a piece of cake. The point now is how to get users to understand these methods and make the data import easier.

First: Use the SELECT INTO statement

If the enterprise database uses SQL Server database, then the SELECT INTO statement can be used to implement the data import. The Select into statement, whose role is to query data from another database and add it to a user-specified table.

In the use of this statement, you need to pay attention to several aspects of the content.

First, you need to establish the relevant tables in the destination database. If you want to import invoicing system database (SQL Server) Products Information table (product) into the ERP System Product Information table (M_PRODUCT). The early stage is in the ERP system database has been established in this product information table.

The second is that this method replicates only the data in the table, not the indexes in the table. such as in the invoicing system data in the product information table, in the product number, product categories and other fields on the establishment of the index. The SELECT INTO statement is used to copy the data into the ERP system's table, only copying the contents of the data itself, without copying the information such as indexes.

Third, the use of this statement has limitations. In general, this can only be used in SQL Server databases. However, for different versions of SQL Server databases, such as 2008 or 2003, are also compatible. If you need to import an object database that is not SQL Server, you need to take another approach.

Four is the use of this statement, in the destination table must not exist data. Otherwise, the data in the destination table is cleared. In other words, this statement does not support merging tables with table data. In SQL Server, there is a similar statement that enables this functionality. The statement is: Insert into. His role is to insert the data from another table into the current table. This statement can be used if the user wants to merge the table with the table data. The two can not be confused enough to use, otherwise, can easily lead to loss of data.

Five of the above two statements support compatible different types of data types. As in the original, the data type of a field is an integer, but the data type of the field in the destination table is a floating-point type, and as long as the two data types are inherently compatible, the database is allowed at the time of import.

Second: The use of Excel and other intermediary tools to control

Although the first method is easier to operate, it has some drawbacks. If he only supports the same type of database, can not interfere with the data too much, and so on. Under normal circumstances, if the user's original data accuracy is relatively high, do not need too much modification can be used directly, then the author has adopted the first way.

However, in the original database, the accuracy of the data is not very high, or, a lot of data is scrapped. In short, the original database data needs to be sorted, only to be able to use the situation, I do not recommend the first import in, and then make changes. In this case, I like to use excle as an intermediary tool. In other words, first the data in the central database into the excle. Some databases, such as Oracle databases, do not support the excle format. However, we can guide it to a file in CSV format. This kind of document Excle also can open.

Then, in Excle, the record is modified. Since Excle is a very strong form-handling software, it is easier to modify data than to modify it directly in the database. If you can use the time to sort and so on, some of the records are not long to clear off. You can also use the replacement function, to change some of the nonstandard characters. These original in the database more complex tasks, in excle and other tools can be easily completed.

When the contents of the table are modified correctly, the database administrator can import the files from the Excle table directly into the SQL Server database. Because SQL Server is born with Excel, the compatibility between them is good. provides tools for importing data directly from an Excel file in SQL Server.

Although this is the use of intermediary tools to import data, but because it is easy to handle, intuitive, so the author in most of the time to use this approach.

Third: Import data Using Data Transformation Services

Data Transformation Services are a very powerful tool available in the SQL Server database. In SQL Server, the Data Transformation feature has a graphical user interface that allows users to import data into the graphical interface and edit the data accordingly.

In addition, the Data Transformation service supports the programming interface of COM components. This means that the Data transformation service can be invoked directly when the foreground application is developed. Let the user through the foreground application system, but not in the background database system to do any operation, you can import data into the database system. In the foreground of the database system to import, there is an obvious benefit, you can advance the legality of the data check. If you can use the script language such as VB to test the data, purify and certain conversion to meet the needs of the destination database.

For example, in the Employee Information table, the Marital Status field, in the Oracle database system, may be 0 or one to represent the marital status. 0 means unmarried, and 1 is married. In a SQL Server database, you use Y or N to represent marital status. Y means married, n means unmarried. When importing data, import data from an Oracle database table directly into a SQL Server database, because marital status This field stores different content types, so it cannot be directly directed. In this case, the data type can be validated using the scripting language before the data is imported. If you do not meet the requirements, you can use the scripting language to convert the data to a certain degree, convert 0 to N, convert 1 to Y, and so on.

So, sometimes when programmers are developing the foreground application, we recommend this Data transformation service to develop the data import function. Not only the existing interface, but also the data can be validated and a certain degree of conversion. In addition, the data import efficiency of Data Transformation service is very high. Even if called through the foreground program, its performance than other methods in the same conditions, to a higher grade. Moreover, with the increase of data quantity, the advantage of data conversion service will become more and more obvious.

However, you need to be aware when the foreground application invokes the Data Transformation service. Data Transformation Services provide a more complex COM interface, so the code for a foreground program to invoke Data Transformation Services is also more complex. If you add some scripting language, it may be more cumbersome to deal with. This interface is generally used only on large systems. If the data is not much, otherwise do not need complex verification and conversion, the use of this interface is a small broadsword, outweigh the gains.

Fourth: Import and export between heterogeneous databases

Although the second, Third Way, can complete the data between heterogeneous databases import and export operations. However, another solution is available in SQL Server. That is, connect directly to a different type of database in a SQL Server database, and then implement the import job of the data by using a statement such as SELECT INTO.

In SQL Server, two functions are provided to help us implement a connection to a non-SQL Server database. These two functions are Opendatesource and OPENROWSET respectively. Their functions are essentially the same, but they differ in detail.

such as Opendatesource this function to be able to open the source database tables and views, and not be able to filter it. If a user only wants to import some of the data from the source table into a table in the SQL Server database, the source table cannot be filtered directly. The filtering action needs to be done in the SQL Server database. and OpenRowset This function, you can open the other database table or view, directly using the where and other conditional restrictions on the record of the new filter. To this end, in the actual application, or OPENROWSET This function is used relatively high frequency.

However, because it requires users to write complex parameters, but also can not provide complex data validation capabilities, so in the actual work of not much. In some small application system, occasionally can see its traces. In some large and mature business software, it is rarely used in this way to import data.

Sometimes, a lot of choices, users do not know how to do. I usually like to use the second way of handling. He is more intuitive, and can be a batch of data changes and collation. However, the disadvantage is that the efficiency is low, especially excle software for processing a large number of records, the speed is relatively slow. If this method does not work, then the author prefers to adopt the data conversion processing method. Although this operation is more complex, but it can provide more complex validation, but also in the graphical interface of the data can be modified, while the efficiency is relatively high.