Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to import data in bulk in the SQL Server database, many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

First, use Select Into statements

If all the enterprise databases use SQL Server database, the Select Into statement can be used to import the data. Select Into statement, whose function is to query the data from another database and add it to a user-specified table.

When using this statement, you need to pay attention to several aspects.

1. The relevant tables need to be created in the destination database first. If you want to import the product information table (Product) from the purchase, sale and storage system database (SQLServer) into the product information table (M_Product) in the ERP system. In the early stage, this product information table has been established in the database of the ERP system.

2. This method only replicates the data in the table, not the index in the table. For example, in the product information table in the purchase, sale and storage system data, an index is established on the fields such as product number, product category and so on. When you use the Select Into statement to copy the data to the table of the ERP system, you only copy the data content itself, but not the index and other information.

3. The use of this statement is limited. In general, this can only be used in SQL Server databases. However, different versions of SQL Server databases, such as 2008 or 2003, are still compatible. If the object database you need to import is not SQL Server, you need to use other methods.

4. If you use this statement, there must be no data in the destination table. Otherwise, the data in the destination table will be purged. That is, this statement does not support the merging of tables and table data. In SQL Server, there is a similar statement that can do this. This sentence is: Insert Into. His role is to insert 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, otherwise, it can easily lead to the loss of data.

5. The above two statements both support different types of compatible data types. For example, in the original standard, 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 already compatible, the database is allowed at the time of import.

Add another tip:

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 11 (only SQL Server is available)

2) select top 0 * into b from a

Second, use Excel and other intermediate tools for control.

Although the first method is relatively simple to operate, it also has some disadvantages. For example, he only supports the same type of database; he cannot interfere with the data too much, and so on. In general, if the accuracy of the original data of the user is relatively high and can be used directly without too much modification, then the author has adopted the first method.

However, if in the original database, the accuracy of the data is not very high, or a lot of data is scrapped. In short, it is necessary to sort out the data of the original database before it can be used. I do not recommend importing it first and then changing it. When I encounter this situation, I like to use Excle as an intermediate tool. In other words, first import the data from the Central Plains database to Excle. Some databases, such as Oracle databases, do not support the Excle format. However, we can export it to a file in CSV format. This kind of file Excle can also be opened.

Then, in Excle, modify the record. Because Excle is a strong table processing software, it is easier to modify its data than to modify it directly in the database. For example, you can use functions such as sorting by time to clear some records that have not been used for a long time. You can also use functions such as substitution to change some non-standard characters. These complex tasks in the database can be easily completed in tools such as Excle.

After the contents of the table are modified correctly, the database administrator can import the files in the Excle table directly into the SQL Server database. Because SQL Server and Excel are born to the same parents, the compatibility between them is very good. Tools are provided in Sql Server to import data directly from Excel files.

Although this requires the help of intermediate tools to import data, because it is convenient and intuitive to handle, I use this approach most of the time. Finally, attach the relevant code on how to make the Excel table visible 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 called excel, you can view the contents of EXEC sp_addlinkedsrvlogin 'excel',' false',' sa', 'Admin', NULL in abc.xls-create a map to allow SQL Server login sa login Admin connection to excel using Excel login And no password-- exec sp_dropserver 'excel'-- delete connection select * from excel...Sheet1 $--check the contents of Excel's Sheet1 table. After reading the above, do you have any further understanding of how to import data in bulk in SQL Server database? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.