syntax, see MSDN url:https://msdn.microsoft.com/zh-cn/library/ms188365.aspx
- BULK INSERT
- [database_name. [Schema_name]. | Schema_name. ] [table_name | view_name]
- From ' data_file '
- [ with
- (
- [[,] batchsize = batch_size] --batchsize directives to set the number of records that can be inserted into a table in a single transaction
- [[,] check_constraints] --Specifies that all constraints on the target table or view must be checked during bulk import operations. Without the check_constraints option, all CHECK and FOREIGN KEY constraints will be ignored, and the table's constraints will be marked as untrusted after this operation.
- [[,] CODEPAGE = { ' ACP ' | ' OEM ' | ' RAW ' | ' Code_page '}] --Specify the code page for the data in the data file
- [[,] DataFileType =
- { ' char ' | ' native ' | ' Widechar ' | ' widenative '}] --Specify BULK INSERT to perform the import operation using the specified data file type value.
- [[,] fieldterminator = ' field_terminator '] --Identifies the symbol separating the content
- [[,] firstrow = First_row] --Specifies the line number of the first row to load. The default value is the first row in the specified data file
- [[,] fire_triggers] --whether to start the trigger
- [[,] formatfile = ' Format_file_path ']
- [[,] keepidentity] --Specify identity values in the import data file for identifying columns
- [[,] keepnulls] --Specifies that empty columns should retain a null value during the bulk-import operation without inserting any default values for the column
- [[,] kilobytes_per_batch = Kilobytes_per_batch]
- [[,] lastrow = Last_row] --Specifies the line number of the last line to load
- [[,] maxerrors = max_errors] --Specifies the maximum number of syntax errors that are allowed to occur in the data, and the bulk import operation is canceled when the number is exceeded.
- [[,] ORDER ({ column [ ASC | DESC ]} [,... N]) ] --Specify how the data in the data file is sorted
- [[,] rows_per_batch = Rows_per_batch]
- [[,] rowterminator = ' row_terminator '] --the symbol that identifies the delimited row
- [[,] TABLOCK] --Specifies that a table-level lock is obtained for the duration of the bulk import operation
- [[,] errorfile = ' file_name '] --Specifies the file that is used to collect rows that are malformed and cannot be converted to an OLE DB rowset.
- )]
Example code, inserting a TXT document into a table, using the tab interval between the fields, and wrapping lines with line breaks.
Bulk Insert dbo.test from ' D:\test.txt ' with ='\t'='\ n ' )
TSQL BULK INSERT Usage
