Batch import data in the SQL Server database

First, 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.

Assume that there is a table table1 with the fields f1 (int) and f2 (varchar (50 )).

The code is as follows:	Copy code
SELECT * INTO table2 FROM table1 after creating a table 2 table, insert all data in Table 1 INTO table 1, you can also change * to f1 or f2 to insert data to an appropriate field. Select into can not only create tables in the same data, but also create tables in different SQL Server databases. USE db1 SELECT * INTO db2.dbo. table2 FROM table1

Second, 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.

Third: 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.

Fourth: 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.