SQL Server Bulk Data import

1. Since 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:

The 
 bcp database name. User name. Table name format nul --  The nul here must exist, For cases where data is not exported and imported,  - f Output formatted file name [ Span style= "color: #ff0000;"  >-x   - C --  [ -t field spacer  ]  [ -r line spacing symbol  ]  -- -T and-R parameters are optional to overwrite the default delimiter specified by-c  Span style= "color: #808080;" >- T  Specifies that the database connection is trusted, that is, login with Windows identity  

2. Bulk Insert

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

Bulk Insert database name. User name. Table name    from ' Data File path '   with   (  =' format file path ',   =  2    -- Specify the number of rows to start in the data file,  default is 1)  

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:

INSERT  into SELECT filed_name_list    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:

SELECT  into temp_table_name    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:

--Create a databaseCREATE DATABASE [Db_mgr]  GO  --Create a test table UseDb_mgrCREATE TABLEdbo. T_student (f_id[int] IDENTITY(1,1) not NULL, F_codevarchar(Ten), F_namevarchar( -), F_memonvarchar( -), F_memo2ntext ,      PRIMARY KEY(f_id))GO    --Populating test DataInsert  intoT_student (F_code, F_name, F_memo, F_memo2)Select  'code001','name001','memo001','Remark 001' Union  All Select  'code002','name002','memo002','Remark 002' Union  All Select  'code003','name003','memo003','Remark 003' Union  All Select  'code004','name004','memo004','Remark 004' Union  All Select  'code005','name005','memo005','Remark 005' Union  All Select  '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:

--Open xp_cmdshell Stored procedure (security hidden after opening)EXECsp_configure'Show advanced Options',1; RECONFIGURE;EXECsp_configure'xp_cmdshell',1; EXECsp_configure'Show advanced Options',0; RECONFIGURE;

3) Use BCP to export format files:

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

4) Export the data file using bcp:

EXEC ' BCP db_mgr.dbo.T_Student out c:/student.data-f c:/student_fmt.xml-t '  truncate Table -- emptying  data in a table

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:

BULK INSERT db_mgr.dbo.T_Student    from ' C:/student.data '   with   (      ='c:/student_fmt.xml'  )  

6) Examples of using OPENROWSET (BULK):

INSERT  intoDb_mgr.dbo.T_Student (F_code, F_name)SELECTF_code, F_name from OPENROWSET(BULKN'C:/student_c.data', FormatFile=N'C:/student_fmt_c.xml') asNew_table_name--t_student table must already exist  SELECTF_code, F_name intodb_mgr.dbo.tt from OPENROWSET(BULKN'C:/student_c.data', FormatFile=N'C:/student_fmt_c.xml') asNew_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

VIA by http://rsljdkt.iteye.com/blog/1147707