BULK INSERT large quantities of data

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’ 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 owner of a table or view. When users perform the bulk copy operation owns the specified table or view, owner is optional. If the owner is not specified and perform the bulk copy operation the user does not own the specified table or view, the Microsoft ® SQL Server ™ will return an error message and cancel the bulk copy operation. ‘Table_name’ bulk copy data in the table or view name. The column can only use those all reference the same base table where the view. Restrictions for copying data to view more information, see INSERT.
‘Data_file’ is the full path of the data file, the data file contains the data to be copied to the specified table or view. BULK INSERT to copy the data from the disk (including network, floppy disk, hard disk, etc.). data_file must specify a valid path from the server that is running SQL Server. If data_file remote file, specify the Universal Naming Convention (UNC) name. BATCHSIZE [= batch_size] the number of rows in the specified batch. Each batch is copied to the server as a single transaction. SQL Server commit or roll back the transaction for every batch (failure). By default, all data in the specified data file is a batch. CHECK_CONSTRAINTS check the table_name any constraints specified in the bulk copy operation. By default, constraints are ignored.
CODEPAGE [= ‘ACP’ | ‘OEM’ | ‘RAW’ | ‘code_page’] Specifies the code page of the data in the data file. , CODEPAGE is relevant only if the data contains character values ??greater than 127 or less than 32 char, varchar, or text columns. CODEPAGE value Description ACP char, varchar, or text data type column for the SQL Server code page conversion from the ANSI / Microsoft Windows ® code page ISO 1252.OEM (default value) char, varchar, or text data type column is converted from the system OEM code page to the SQL Server code page.RAW is not the conversion from one code page to another code page; This is the fastest option. specific code_page code page number, such as 850. DATAFILETYPE [= {‘char’ | ‘native’ | ‘widechar’ | ‘widenative’}] specify the BULK INSERT use the default values ??specified to perform the copy operation. DATAFILETYPE Value Description char (default) Performs the bulk copy operation from the data file containing character data. native use of native (database) data types to perform the bulk copy operation. The data file to be loaded by bulk copy data to create the copy from SQL Server using the bcp utility. widechar perform the bulk copy operation from the data file containing Unicode characters. widenative perform the same native bulk copy operation, the only difference is the char, varchar, and text columns in the data file is stored as Unicode. The data file to be loaded by bulk copy data to create the copy from SQL Server using the bcp utility. This option is a higher performance alternative the widechar option, and it is used to using a data file from a running SQL Server computer to transfer data to another computer. When the transmission of data that contains ANSI extended characters, use this option to take advantage of the performance of native mode.
FIELDTERMINATOR [= ‘field_terminator’] Specifies the field terminator is used to the char and widechar data file. The default field terminator / t (tabs).
FirstRow [to = first_row] you want to copy the first line number. The default value is 1, which means that the first line in the specified data file. No insert triggers defined in the FIRE_TRIGGERS specify the destination table will perform the bulk copy operation. If you do not specify a FIRE_TRIGGERS is, will not perform any insert triggers. FORMATFILE [= ‘format_file_path’] specify a format file’s full path.Format file describes the data files containing the memory response, these storage response is created using the bcp utility on 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 in a different order. Column delimiters change. Data format other changes. Typically, the format file bcp utility to create and modify as needed with a text editor. For more information, see bcp Utility.
KEEPIDENTITY specify the identity column value exists in the import file. If there is no specify the KEEPIDENTITY, the identity values ??for this column in the imported data file will be ignored, and the SQL Server will be created according to the table specified in the seed and increment value is automatically assigned to a unique value. If the data file does not contain the identity column in the table or view, use a format file to specify the identity column in the table or view should be ignored when importing data; SQL Server automatically for this column gives a unique value. For more information, please see the DBCC CHECKIDENT.
KEEPNULLS Specifies the bulk copy operation that empty columns should retain a null value, rather than given default values ??of the inserted column. Specified in each batch data KILOBYTES_PER_BATCH [= kilobytes_per_batch] approximate number of kilobytes (KB).The default KILOBYTES_PER_BATCH unknown.
LASTROW [= last_row] you want to copy the last line number. The default value is 0, which means that the last row in the specified data file. MAXERRORS [= max_errors] specifies the maximum number of errors that may arise before the bulk copy operation canceled. Can not be the bulk copy operation to import each row will be ignored and is counted as one error. If not specified max_errors, the default value is 0. Order ({column [ASC | DESC]} [, … n]) how the data in the specified data file sort. If the loaded data are sorted according to the clustered index in the table, you can improve the performance of the bulk copy operation. If the data file is based on a different order, or the table does not have a clustered index, the ORDER clause is ignored. The column names must be valid column in the destination table. By default, the bulk insert operation assumes the data file is not sorted. n is a placeholder can specify more than one column.
Specify the number of rows of each batch of data in ROWS_PER_BATCH [= rows_per_batch] (ie rows_per_bacth). When no specified BATCHSIZE when used, resulting in the entire data file is sent to the server as a single transaction. Server under the rows_per_batch optimizes the bulk load. The default ROWS_PER_BATCH unknown. ROWTERMINATOR [= ‘row_terminator’] designated for the char and widechar data file you want to use the row terminator. The default value is / n (newline). The TABLOCK specified for a table-level locks during bulk copy operations. If the table has no indexes and TABLOCK is specified, then the table can simultaneously by multiple clients loaded. By default, locking behavior is determined by the table option table lock on bulk load. Control lock will reduce the lock contention on the table during the bulk copy operation, which greatly improves performance. Notes BULK INSERT statement can be executed in a user-defined transaction. For a BULK INSERT statement and BATCHSIZE clause the data loaded into the table or view using multiple batches of user-defined transaction rollback will roll back all messages sent to SQL Server batch. Permissions Only members of the sysadmin and bulkadmin fixed server role can perform BULK INSERT. Example This example from the specified data file import order details, the file using the vertical bar (|) character as the field terminator use | / n as the row terminator.
BULK INSERT Northwind.dbo. [Order Details] FROM ‘f :/ orders / lineitem.tbl’ WITH (FIELDTERMINATOR = ‘|’, ROWTERMINATOR = ‘| / n’)
The example specifies FIRE_TRIGGERS parameters.
BULK INSERT Northwind.dbo. [Order Details] FROM ‘f :/ orders / lineitem.tbl’ WITH (FIELDTERMINATOR = ‘|’, ROWTERMINATOR = ‘:/ n’, FIRE_TRIGGERS)