How to batch import data in the SQL Server database

During the implementation of software projects, data import has always been a headache for the project staff. In fact, many data import methods are integrated in SQL Server. Some project implementation consultants have a headache. In the eyes of our database administrators, it is a piece of cake. The focus is on how to make data import easier by letting users understand these methods.

Method 1: Use the Select Into statement

If enterprise databases use SQL Server databases, you can use the Select Into statement to import data. The Select Into statement is used to query data from another database and add it to a table specified by a user.

When using this statement, pay attention to several aspects.

First, you must create related tables in the target database. If you want to import the Product information table in the SQL Server database to the Product information table (M_Product) in the ERP system. This product information table has been created in the database of the ERP system in the early stage.

Second, this method only copies the data in the table, instead of the indexes in the table. For example, in the product information table of the invoicing system data, an index is created on the product number, product type, and other fields. When the Select Into statement is used to copy data to the table of the ERP system, the data content is copied instead of the index and other information.

Third, this statement has limitations. Generally, this can only be used in SQL Server databases. However, different versions of SQL Server databases, such as MySQL 2008 or MySQL 2003, are compatible. If the object database to be imported is not from SQL Server, other methods are required.

4. if this statement is used, no data exists in the target table. Otherwise, the data in the target table is cleared. That is to say, this statement does not support table and table data merging. In SQL Server, a similar statement can be used to implement this function. This statement is Insert. It is used to insert data from another table to the current table. If you want to merge the table and table data, you can use this statement. The two cannot be used together. Otherwise, data may be lost.

Fifth, both statements support compatible data types. For example, in the original tag, the Data Type of a field is integer, but in the destination table, the Data Type of this field is floating point. As long as the two data types are compatible, the database is allowed during import.

Method 2: use Excel and other intermediate tools for control

Although the first method is relatively simple, it also has some disadvantages. For example, it only supports the same type of database, and cannot perform too many data interventions. Under normal circumstances, if the user's original data is relatively accurate and can be used directly without too many modifications, the author has adopted the first method.

However, in the original database, the data accuracy is not very high, or a lot of data is decommissioned. In short, you need to sort out the data of the original database before you can use the data. I do not recommend that you import the data first before making changes. In this case, I like to use Excle as an intermediate tool. That is to say, first import the data from the original database to the Excle. Some databases, such as Oracle databases, do not support the Excle format. However, we can export it to a CSV file. This type of file Excle can also be opened.

Then, modify the record in Excle. As Excle is a powerful table processing software, it is easier to modify data than to directly modify data in a database. For example, you can use time-based sorting to clear records that are not used for a long time. You can also use replacement and other functions to change some nonstandard characters. These previously complex tasks in the database can be easily completed in tools such as Excle.

After the table content is modified correctly, the database administrator can directly import the files in the Excle table to the SQL Server database. Since SQL Server and Excel are the same parent, they have good compatibility. SQL Server provides a tool to import data directly from an Excel file.

Although it is necessary to use an intermediate tool to import data, because it is convenient and intuitive to process, I usually use this method most of the time.

Method 3: Use the data conversion service to import data

Data conversion service is a powerful tool provided by SQL Server database. In SQLServer, the data conversion function has a graphical user interface. You can import data in the graphic interface and edit the data accordingly.

In addition, the data conversion service also supports programming interfaces of COM components. This means that the data conversion service can be called directly during front-end application development. You can import data to the database system without performing any operations on the background database system. Importing the database system at the front-end has an obvious advantage that you can check the validity of the data in advance. For example, you can use scripts such as VB to test, purify, and convert the data to meet the needs of the target database.

For example, in the employee information table, the Marital Status field may be 0 or 1 in the Oracle database system. 0 indicates unmarried, 1 indicates married. In the SQL Server database, Y or N is used to indicate marital status. Y indicates married, N indicates unmarried. When importing data, if you directly import the data in the Oracle database table to the SQL Server database, because the Marital Status field stores different content types, you cannot directly import the data. In this case, you can use the script language to verify the data type before importing data. If it does not meet the requirements, you can use the script language to convert the data to a certain degree, convert 0 to N, and convert 1 to Y.

Therefore, we recommend that you use this data conversion Service to develop data import functions when developing front-end applications. There are not only ready-made interfaces, but also data verification and conversion to a certain extent. In addition, the data import efficiency of the data conversion service is very high. Even if it is called through the foreground program, its performance is higher than that of other methods under the same conditions. In addition, as the data volume increases, the advantages of the data conversion service become more and more obvious.

However, when the front-end application calls the data conversion service, pay attention to it. The COM interface provided by the data conversion service is complicated, so the code for calling the data conversion service by the foreground program is also complicated. If some scripting languages are added, the processing may be more complicated. Therefore, this interface is generally used only on large systems. If there is not much data, or complex verification and conversion are not required, this interface is not worth the candle.

Method 4: Import and Export between heterogeneous databases

Both the second and third methods can import and export data between heterogeneous databases. However, SQL Server provides another solution. That is, directly connect to other types of databases in the SQL Server database, and then use Select Into and other statements to import data.

In SQL Server, two functions are provided to help us connect non-SQL Server databases. The two functions are Opendatesource and Openrowset respectively. Their functions are basically the same, but they differ in details.

For example, the Opendatesource function can be used to create tables and views of open source databases, but cannot be used to filter them. If you only want to import some data from the source table to the SQL Server database table, you cannot directly filter the source table. The filtering action must be performed in the SQL Server database. The Openrowset function can directly filter records by using conditional restrictions such as Where when opening the tables or views of the target database. Therefore, Openrowset is frequently used in practical applications.

However, because it requires users to write complex parameters and does not provide complex data verification functions, not many are used in actual work. In some small application systems, we can see it occasionally. This method is rarely used in some mature commercial software to import data.

Sometimes, the user does not know how to start when there are too many options. I usually prefer the second method. It is intuitive and can modify and organize data in batches. However, the defect is that the efficiency is relatively low, especially when Excle software processes a large number of records, the speed is relatively slow. If this method does not work, I prefer data conversion. Although this operation is complex, it can provide more complex verification, and it can modify data in a graphical interface, and the efficiency is also relatively high.

1. SQL Server CLR code debugging in Visual Studio
2. Simple Method for reading XML files in SQL Server
3. Optimize SQL Server database query methods