Copy a data file to a database table or view in the format specified by the user.

BULK INSERT

Copy a data file to a database table or view in the format specified by the user.

Fieldterminator [= 'Field_terminator']

UsedCharAndWidecharThe field terminator of the data file. The default field Terminator is \ t (Tab ).

Firstrow [= First_row]

Specifies the row number of the first row to be copied. The default value is 1, indicating the first row of the specified data file.

Fire_triggers

Any insert trigger defined in the target table will be executed during the large-capacity copy operation. If fire_triggers is not specified, no insert trigger is executed.

Formatfile [='Format_file_path']

Specifies the full path of a formatted file. The format file describes the data files containing the storage response.BCPThe utility is created in the same table or view. The format file should be used in the following situations:

• Data files contain more or fewer columns than tables or views.

• Columns use different sequence.
• The column Delimiter is changed.
• There are other changes to the data format. Normally, the format file passes throughBCPThe utility is created and modified in a text editor as needed. For more information, see BCP utility.

Keepidentity

The value of the specified ID column exists in the import file. If no keepidentity is specified, the value of this column is ignored in the imported data file, SQL Server automatically assigns a unique value based on the seed value and increment value specified during table creation. If the data file does not contain the ID column in the table or view, a format file is used to specify that the ID column in the table or view should be ignored during data import; SQL Server automatically assigns a unique value to this column. For more information, see DBCC checkident.

Keepnulls

Specify that a null value should be retained for the hollow column in the large-capacity copy operation, instead of assigning the default value to the inserted column.

Kilobytes_per_batch [= Kilobytes_per_batch]

Specify the approximate number of kilobytes (Kb) of data in each batch ). By default, kilobytes_per_batch is unknown.

Lastrow [= Last_row]

Specifies the row number of the last row to be copied. The default value is 0, indicating the last row in the specified data file.

Maxerrors [=Max_errors]

Specifies the maximum number of errors that may occur before the large-capacity copy operation is canceled. Each row that cannot be imported by the large-capacity copy operation will be ignored and counted as an error. If noMax_errorsThe default value is 0.

Order({Column[ASC | DESC]} [,...N])

Specifies how the data in the data file is sorted. If the loaded data is sorted by the clustered index in the table, the performance of large-capacity replication operations can be improved. If data files are sorted in different order or the table does not have clustered indexes, the Order clause is ignored. The column name must be valid in the target table. By default, the large-capacity insert operation assumes that the data files are not sorted.

N

Yes indicates that you can specify placeholders for multiple columns.

Rows_per_batch [=Rows_per_batch]

Specifies the number of rows (that isRows_per_bacth). If the batchsize is not specified, the entire data file is sent to the server as a single transaction. ServerRows_per_batchOptimized large-capacity loading. By default, rows_per_batch is unknown.

Rowterminator [= 'Row_terminator']

SpecifyCharAndWidecharThe row Terminator used by the data file. The default value is \ n (line break ).

Tablock

Specifies to obtain a table-Level Lock during a large-capacity copy operation. If the table has no index and tablock is specified, the table can be loaded by multiple clients at the same time. By default, the lock action is performed by the table option.Table Lock On Bulk Load. Controlling the lock only during the large-capacity replication operation will reduce the table lock contention and greatly improve the performance.

Example:

Import IIS log files to the database

1 # Software: Microsoft Internet Information Services 5.1
2 # Version: 1.0
3 # Date: 2006 - 04 - 22   22 : 56 : 28
4 # Fields: Date time C-ip cs-username CS-method CS-Uri-stem SC-status SC-bytes CS-version CS (User-Agent) CS (Referer)
5 2006 - 04 - 22   22 : 56 : 28   127.0.0.1 -Get/cepsmis/ 302   287 HTTP/ 1.1 Mozilla/ 4.0 + (Compatible ; + MSIE + 6.0; + windows + nt + 5.1; + sv1; + Maxthon; +. NET + CLR + 1.1.4322 )-

 

1 Bulk   Insert Pubs .. ex From   ' C: \ windows \ system32 \ logfiles \ w3svc1 \ exyymmdd. Log '
2 With (
3 Firstrow =   2 ,
4 Datafiletype =   ' Char ' ,
5 Fieldterminator =   '   ' ,
6 Rowterminator =   ' \ N '
7 )