2 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. Believe that the following methods have been used, the warm know new ha, if there is a better way to hope that we all put forward ~

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.

1, need to establish the relevant table in the destination database first. 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.

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

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

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

5, the above two statements all 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.

Another tip:

When we want to copy all the field names from table A to a new table B, you can use the following methods:

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

2 Select top 0 * to B from a

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 out before it can be used, I do not recommend first import in, then make changes. I like to use excle as an intermediate tool when I encounter this situation. 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 will take the intermediary tool to import data, but because it is easy to handle, intuitive, so I am in most of the time to use this approach. Finally, attach the relevant code for how Excel tables can be found in the database:

exec sp_addlinkedserver ' 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 that can view content inside Abc.xls

EXEC sp_addlinkedsrvlogin ' Excel ', ' false ', ' sa ', ' Admin ' , NULL--Creates a mapping to allow SQL Server login SA to connect to Excel using Excel login Admin, and no password

--exec sp_dropserver ' Excel '--delete connection

Select * FROM Excel ... sheet1$--View the contents of an Excel Sheet1 table

Tips: Excel tables in the data, you must first remove the space, otherwise it may be due to space reasons, resulting in inconsistent data. The name of the Excel table, to be set to Abc.xls, and placed on the desktop. When you open a specified Excel table, you cannot view its data in the database or the database will complain.

Sometimes, a lot of choices, users do not know how to do. I usually like to use the second way of handling. It 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.

Therefore, in the use of the process, according to personal habits, preferences to choose their own proficiency in the operation, but also to specific problems specific analysis.