SQL Server Bulk Import

SQL Server's BULK INSERT statement can import local or remote data files into a database in batches, very quickly. The remote file must be shared before the file path should use the Universal Convention (UNC) name, which is the form of "\ \ server name or ip\ share name \ path \ file name."

* 1. Because BULK insert is often more convenient to bulk import data with format files, here's how the BCP tool exports format files.

BCP is a command-line utility provided by SQL Server that provides the ability to export, import, and format file export of data, and the syntax for exporting a format file is as follows:

SQL code

1. BCP database name. The name of the user. Table name format nul--nul must exist here, for cases where data is not exported and imported
2. -F output Format filename [-X]-C---x parameter specifies the output format file is in XML format (default non-XML format); The-c parameter specifies that the data is stored as a character and by default specifies ' \ t ' as the field spacer; ' \ n ' As the line delimiter
3. [-t Field spacer] [-R line spacing symbol]---T and-R parameters are optional to overwrite the default delimiter specified by-C
4. -T-Specifies that the database connection is trusted, that is, log on with Windows identity

* 2. Bulk Insert

Import the data file according to the format file, the syntax format is as follows:

SQL code

1. Bulk Insert database name. User name. Table name
2. From ' Data file path '
3. With
4. (
5. formatfile = ' Format file path ',
6. FirstRow = 2--Specifies the number of rows to start in the data file, which is 1 by default
7. )

* 3. Openrorwset (BULK) function

Sometimes, you can use the OPENROWSET (BULK) function to more flexibly select the fields you want to insert into the original table or other tables, with the syntax in the following format:

SQL code

1. INSERT into To_table_name SELECT filed_name_list
2. From OPENROWSET (BULK N ' path_to_data_file ', formatfile=n ' Path_to_format_file ') as New_table_name

Of course, this function can also be used:

SQL code

1. SELECT field_name_list into Temp_table_name
2. From OPENROWSET (BULK N ' path_to_data_file ', formatfile=n ' Path_to_format_file ') as New_table_name

Here is a complete example:

1) Create a database, a table, and populate the test data with the following script:

SQL code

2. --Create a database
4. CREATE DATABASE [Db_mgr]
6. GO
8. --Create a test table
10. Use Db_mgr
12. CREATE TABLE dbo. T_student (
14. f_id [int] IDENTITY (*) is not NULL,
16. F_code varchar (10),
18. F_name varchar (100),
20. F_memo nvarchar (500),
22. F_memo2 ntext,
24. PRIMARY KEY (f_id)
26. )
28. GO
32. --Filling test data
34. Insert into T_student (F_code, F_name, F_memo, F_memo2) Select
36. ' code001 ', ' name001 ', ' memo001 ', ' remarks 001 ' UNION ALL Select
38. ' code002 ', ' name002 ', ' memo002 ', ' remarks 002 ' UNION ALL Select
40. ' code003 ', ' name003 ', ' memo003 ', ' remarks 003 ' UNION ALL Select
42. ' code004 ', ' name004 ', ' memo004 ', ' remarks 004 ' UNION ALL Select
44. ' code005 ', ' name005 ', ' memo005 ', ' remarks 005 ' UNION ALL Select
46. ' code006 ', ' name006 ', ' memo006 ', ' remark 006 '

2) We can use the SQL Server master: The xp_cmdshell stored procedure passes CMD commands to the system, allowing you to enter BCP commands directly in the SQL Server query processor without switching to command mode execution. SQL Server has disabled the stored procedure by default for security purposes, and is opened by the following method:

SQL code

1. --Open xp_cmdshell stored procedure (security hidden after opening)
2. EXEC sp_configure ' show advanced options ', 1;
3. RECONFIGURE; EXEC sp_configure ' xp_cmdshell ', 1;
4. EXEC sp_configure ' show advanced options ', 0;
5. RECONFIGURE;

3) Use BCP to export format files:

SQL code

1. EXEC Master. xp_cmdshell ' BCP db_mgr.dbo.T_Student format nul-f c:/student_fmt.xml-x-c-t '

4) Export the data file using bcp:

SQL code

1. EXEC Master. xp_cmdshell ' BCP db_mgr.dbo.T_Student out c:/student.data-f c:/student_fmt.xml-t '
2. TRUNCATE TABLE Db_mgr.dbo.T_Student--emptying data from tables

Note: In the actual use of the process, the data files can be generated by the program, such as Log records!

5) Bulk import of data files using the BULK INSERT statement:

SQL code

1. BULK INSERT db_mgr.dbo.T_Student
2. From ' C:/student.data '
3. With
4. (
5. FormatFile = ' C:/student_fmt.xml '
6. )

6) Examples of using OPENROWSET (BULK):

SQL code

1. INSERT into Db_mgr.dbo.T_Student (F_code, f_name) SELECT F_code, F_name
2. From OPENROWSET (BULK N ' c:/student_c.data ', formatfile=n ' C:/student_fmt_c.xml ') as New_table_name--t_student table must already exist
4. SELECT F_code, f_name into Db_mgr.dbo.tt
5. From OPENROWSET (BULK N ' c:/student_c.data ', formatfile=n ' C:/student_fmt_c.xml ') as New_table_name--TT table can not exist

Reference:

Use BULK INSERT or OPENROWSET (BULK ...) to import bulk data (especially in the section on security, which should be particularly important when importing remote files):

Http://msdn.microsoft.com/zh-cn/library/ms175915.aspx

To create a format file:

Http://msdn.microsoft.com/zh-cn/library/ms191516.aspx

OPENROWSET (Transact-SQL):

Http://msdn.microsoft.com/zh-cn/library/ms190312.aspx

BULK INSERT (Transact-SQL):

Http://msdn.microsoft.com/zh-cn/library/ms188365.aspx

bcp utility:

Http://msdn.microsoft.com/zh-cn/library/ms162802.aspx

SQL Server Bulk Import