Transact-SQL Reference
Bulk INSERT
Copy a data file to the database table or view in the format specified by the user.
grammar
Bulk INSERT [['Database_name'.] ['Owner'].] {'Table_name' from 'Data_File'} [with ([BatchSize [= Batch_size]] [[,] Check_constraints] [[,] CodePage [= 'ACP '|' OEM '|' RAW '|' Code_page ']] [[,] DataFileType [= {' CHAR '|' Native '|' WideChar '|' Widenative '}] [[,] Fieldterminator [=' Field_Terminator '] [,] Firstrow [= first_ROW]] [[,] Fire_triggers] [[,] formatfile =' format_file_path '] [[,] keepIdentity] [[,] KEEPNULLS] [[,] kilobytes_per_batch [= kilobytes_per_batch] ] [[,] Lastrow [= last_row]] [[,] MaxErrorS [= max_errors]] [[,] order ({Column [ASC | DESC]} [, ... n])] [[,] rows_per_batch [ = ROWS_PER_BATCH]] [[,] rowterminator [= 'row_terminator'] [, [Tablock]]) ]
parameter
'Database_name'
It is the name of the database that contains the specified table or view. If not specified, the system defaults to the current database.
'Owner'
Is the name of the table or view owner. Owner is optional when the user who performs a large-capacity replication operation has a specified table or view. If the user does not specify Owner and perform a large-capacity replication operation does not have a specified table or view, Microsoft® SQL ServerTM will return an error message and cancel a large-capacity replication operation.
'Table_name'
It is the name of the table or view of the large-capacity copy data. You can only use the views where all columns are referenced to the same base table. For more information on copying data to the view, see INSERT.
'Data_File'
It is the full path to the data file, which contains data to be copied to the specified table or view. Bulk INSERT is replicated from disk (including networks, floppy disks, hard drives, etc.).
Data_file must specify a valid path from the server running SQL Server. If Data_File is a remote file, specify the General Name Rules (UNC) name. Batchsize [= batch_size]
Specify the number of rows in batch. Each batch is copied as a transaction to the server. SQL Server submits or rolls back (when failure), each batch transaction. By default, all data in the specified data file is a batch.
Check_constraints
Specifies any constraints for checking Table_name in a large capacity replication operation. By default, constraints will be ignored.
CodePage [= 'ACP' | 'OEM' | 'RAW' | 'CODE_PAGE']
Specifies the code page of the data in the data file. CODEPAGE is only applicable only when the data contains a character value greater than 127 or less than 32.
CodePage Value Description Acpchar, VARCHAR, or Text Data Type Column The column of the ANSI / Microsoft Windows® code page ISO 1252 is converted to the SQL Server code page. The column of OEM (default) Char, VARCHAR, or TEXT data type is converted from the system OEM code page to the SQL Server code page. RAW does not conversion from a code page to another; this is the fastest option. Code_page Specific code page number, for example 850.
DataFileType [= {'char' | 'native' | 'widechar' | 'widenative'}]
Specifies whether Bulk INSERT performs a replication operation using the specified default value.
DataFileType Value Description Char (Default) Performs a large-capacity replication operation from a data file containing character data. Native uses the Native data type to perform a large-capacity replication operation. The data file to be loaded is created by large capacity copy data, which is based on the BCP utility from SQL Server. WideChar performs a large-capacity replication operation from a data file containing Unicode characters. Widenative executes the same mass replication operation as Native, and the difference is CHAR, VARCHAR, and TEXT columns in the data file to Unicode. The data file to be loaded is created by large capacity copy data, which is based on the BCP utility from SQL Server. This option is a higher performance alternative to the WideChar option, and it is used to use a data file from a computer running SQL Server to another computer. When data containing an ANSI extended character is transmitted, this option is used to take advantage of NATIVE mode performance.
FIELDTERMINATOR [= 'Field_Terminator']
Specifies the field terminator for char and widechar data files. The default field terminator is / t (tab).
FIRSTROW [= first_ROW]
Specifies the line number of the first line to be copied. The default is 1, indicating the first line of the specified data file.
FIRE_TRIGGERS
Any insert trigger defined in the specified destination table will be executed during a large capacity replication operation. If Fire_triggers are not specified, any plug-in trigger will not be executed.
Formatfile [= 'format_file_path']
Specifies the full path to a format file. The format file describes a data file containing a storage response, which is created using the BCP utility in the same table or view. The format file should be used for the following: Data file contains more or fewer columns than the table or view. Columns use different order. The column segmentation changes. Data formats have other changes. Typically, the format file is created by the BCP utility and modifies the text editor as needed. For more information, see the BCP utility.
KeepIdentity
The value of the specified identity column is stored in the import file. If not specified KeePidentity, the identifier value of this column will be ignored in the imported data file, and SQL Server automatically assigns a unique value based on the seed value and increment value specified when creating. If the data file does not contain the identity column in the table or view, use a format file to specify that the identity column in the table or view is ignored when importing data; SQL Server automatically assigns unique values to this column. See DBCC Checkident for more information.
KeepnUlls
Specifies that a null value should be retained in a large-capacity replication operation, not the default value to the inserted column.
Kilobytes_Per_batch [= kilobytes_per_batch]
Specifies the approximate kilobytes of data in each batch (KB). By default, kilobytes_per_batch is unknown.
Lastrow [= last_row]
Specifies the line number of the last row to copy. The default is 0, indicating the last line in the specified data file.
MaxerRors [= max_errors]
Specifies the maximum number of errors that may be generated before the mass replication operation is canceled. Each row that cannot be imported by a large capacity replication operation will be ignored and is called an error. If you do not specify max_errors, the default is 0.
ORDER ({Column [ASC | DESC]} [, ... n])
Specifies how the data in the data file is sorted. If the loaded data is sorted according to the aggregation index in the table, the performance of the large capacity replication operation can be improved. If the data file is sorted in different order, the ORDER clause will be ignored or the ORDER clause will be ignored. The given column name must be a valid column in the target table. By default, large-capacity insertions assume that the data file is not sorted.
n
It means that the placeholders that can specify multiple columns.
Rows_Per_batch [= rows_per_batch]
Specifies the number of rows that process data (ie rows_per_bacth). When you do not specify BatchSize, it is used to send the entire data file as a single transaction to the server. The server optimizes large-capacity loads according to ROWS_PER_BATCH. By default, rows_per_batch is unknown.
Rowterminator [= 'Row_Terminator']]
Specifies the row terminator to use for a char and widechar data files. The default is / n (newline).
Tablock
Specifies a table-level lock for a large-capacity replication operation. If the table has no index and specifies TabLock, the table can be loaded simultaneously by multiple clients. By default, the lock behavior is determined by the table option Table Lock on bulk load. The control lock will reduce the lock competition in the table during large-capacity replication operations, which greatly improves performance.
Comment
BULK INSERT statements can be executed in user definition transactions. For a BULK INSERT statement and the BATCHSIZE clause to load data to a user-defined transaction in a table or view of multiple batch, it will roll back all the batch of all sent to SQL Server.
Authority
Only Sysadmin and Bulkadmin fixed server roles can execute Bulk INSERT.
Example
This example imports order details from the specified data file, which uses the vertical (|) character as a field terminator, using | / N as the row terminator. Bulk INSERT Northwind.dbo. [Order Details]
From 'f: /orders/lineItem.tbl'
WITH
(
FIELDTERMINATOR = '|',
Rowterminator = '| / n'
)
This example specifies the fire_triggers parameter.
Bulk INSERT Northwind.dbo. [Order Details]
From 'f: /orders/lineItem.tbl'
WITH
(
FIELDTERMINATOR = '|',
RowTerminator = ': / n',
FIRE_TRIGGERS
)
See
BCP Utility Sort Rules Reproduction Data between Different Sort Rules Use BCP and BULK INSERT Parallel Data to load sp_tableOption use format files
© 1988-2000 Microsoft Corporation. all rights reserved.
