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