BULK INSERT

Imports a data file into a database table or view in a user-specified format in SQL Server.

Arguments

'data_file'

Is the full path of the data file that contains data to import into the specified table or view. BULK INSERT can import data from a disk (including network, floppy disk, hard disk, and so on).

data_file must specify a valid path from the server on which SQL Server is running. If data_file is a remote file, specify the Universal Naming Convention (UNC) name. A UNC name has the form \\Systemname\ShareName\Path\FileName. For example, \\SystemX\DiskZ\Sales\update.txt. 

BATCHSIZE = batch_size

Specifies the number of rows in a batch. Each batch is copied to the server as one transaction. If this fails, SQL Server commits or rolls back the transaction for every batch. By default, all data in the specified data file is one batch. 

CHECK_CONSTRAINTS

Specifies that all constraints on the target table or view must be checked during the bulk-import operation. Without the CHECK_CONSTRAINTS option, any CHECK and FOREIGN KEY constraints are ignored, and after the operation, the constraint on the table is marked as not-trusted. UNIQUE, and PRIMARY KEY constraints are always enforced. When importing into a character column that is defined with a NOT NULL constraint, BULK INSERT inserts a blank string when there is no value in the text file.

FIELDTERMINATOR = 'field_terminator'

Specifies the field terminator to be used for char and widechar data files. The default field terminator is \t (tab character

FIRSTROW = first_row

Specifies the number of the first row to load. The default is the first row in the specified data file. FIRSTROW is 1-based.

KEEPIDENTITY

Specifies that identity value or values in the imported data file are to be used for the identity column. If KEEPIDENTITY is not specified, the identity values for this column are verified but not imported and SQL Server automatically assigns unique values based on the seed and increment values specified during table creation. If the data file does not contain values for the identity column in the table or view, use a format file to specify that the identity column in the table or view is to be skipped when importing data; SQL Server automatically assigns unique values for the column.

MAXERRORS = max_errors

Specifies the maximum number of syntax errors allowed in the data before the bulk-import operation is canceled. Each row that cannot be imported by the bulk-import operation is ignored and counted as one error. If max_errors is not specified, the default is 10.

ROWTERMINATOR = 'row_terminator'

Specifies the row terminator to be used for char and widechar data files. The default row terminator is \r\n(newline character).

TABLOCK

Specifies that a table-level lock is acquired for the duration of the bulk-import operation. A table can be loaded concurrently by multiple clients if the table has no indexes and TABLOCK is specified. By default, locking behavior is determined by the table option table lock on bulk load. Holding a lock for the duration of the bulk-import operation reduces lock contention on the table, in some cases can significantly improve performance.

Note     When using a format file with BULK INSERT, you can specify up to 1024 fields only. This is same as the maximum number of columns allowed in a table. If you use BULK INSERT with a data file that contains more than 1024 fields, BULK INSERT generates the 4822 error. The bcp utility does not have this limitation, so for data files that contain more than 1024 fields, use the bcp command.

Requires INSERT and ADMINISTER BULK OPERATIONS permissions.

BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail

   FROM 'f:\orders\lineitem.tbl'

   WITH

      (

         FIELDTERMINATOR =' |',

         ROWTERMINATOR =' |\n'

      );