Two Methods for batch data import in SQL Server databases: SQL Server

Two Methods for batch data import in SQL Server databases: SQL Server

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. I believe that all of the following methods have been used, and I hope you can come up with a better method ~

I. 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.

1. related tables must be created first 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.

2. 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.

3. 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 easily.

5. the preceding two 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.

In addition, add the following tips:

When we want to copy all the field names of Table a to a new table B, we can use the following methods:

1) select * into B from a where 1 <> 1 (available only for SQL Server)

2) select top 0 * into B from

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 the data of the original database before you can use the data. We 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 the 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 this requires the use of intermediate tools to import data, it is convenient and intuitive to process, so I usually use this method most of the time. Finally, I will attach the relevant code that allows the Excel table to be viewed in the database:

Exec sp_addmediaserver 'excel ', 'ex', 'Microsoft. jet. OLEDB.4.0 ', 'c: \ Documents and Settings \ Administrator \ Desktop \ abc.xls', null, 'excel 5.0 '--create a connection service named Excel. You can copy the content in abc.xls to EXEC sp_add1_srvlogin 'excel', 'false ', 'sa ', 'admin', NULL -- creates a ing to allow SQL Server to log on to sa and use Excel to log on to Admin to connect to excel, no Password -- exec sp_dropserver 'excel '-- Delete Connection select * from excel... sheet1 $ -- view the content in Sheet 1 in Excel

TIPS: Remove spaces for data in an Excel file. Otherwise, data inconsistency may occur. The name of the exceltable, which must be set to abc.xls and placed on the desktop. When opening a specified Excel table, you cannot view its data in the database, or the database reports an error.

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 be used to 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.

Therefore, in the process of use, you can select your preferred skills based on your personal habits and preferences, but you also need to analyze specific problems.