Bulk INSERT loads the data from the data file to the table. This feature is similar to the IN option of the BCP command, but the data file is read by the SQL Server process.
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.
In SQL Server 2005, Bulk INSERT performs new and more stringent data authentication and data checks for data read from the file, so when performing verification and checking for invalid data, it may cause an existing script to fail. For example, Bulk INSERT is now verified:
Native representation of the Float or REAL data type is effective. The number of bytes in the Unicode data length is even.
Invalid data can be imported in the early versions of SQL Server, but may not be loaded now. In earlier versions, an error will occur when the client tries to access invalid data. When querying data after large capacity loading, the added verification can minimize warnings.
Example:
A. Import data from files using pipelines
This example uses the order details into the specified data file into the AdventureWorks.sales.salesOrderDetail table, and use the vertical line (|) as a field terminator, use the | / N as the row terminator. Bulk INSERT AdventureWorks.Sales.salesOrderDetail from 'f: /orders/lineItem.tbl' with (Fieldterminator = '|', Rowterminator = '| / n')
B: Text format file import
Data are as follows:----------------------------------------------- -------------------------------------- 000001; Deep development; 2006-06-12; Continuous suspension , The trend is very good, continue to hold 000002; Vanke; 2006-06-12; Putting volume down, pay attention to indicators, pay attention to avoid 000817; Liaohe Oilfield; 2006-06-12; Continuous suspension, time limited, will be refunded 000818; Jinhua Chlor-alkali; 2006-06-12; Dish fell clear, still have space, pay attention to avoid
---- Construction Table Create Table TMP_TXT_VENDOR (A1 VARCHAR (200) Null, A2 VARCHAR (200) Null, A3 VARCHAR (200) NULL, A4 VARCHAR (200) NULL) DECLARE @SrcFile As Varchar (20), @fieldterminator1 AS VARCHAR (10), @ rowterminator1 as varchar (20) Select @ srcfile = 'c: /test.txt', @fieldterminator1 = ';', @ rowterminator1 = '/ N'Exec (' bulk INSERT TMP_TXT_VENDOR FROM '' " @ srcfile '' 'with (Fieldterminator =' '' @ fieldterminator1 '', Rowterminator = '' ' @ rowterminator1 ' ')') SELECT * from TMP_TXT_VENDOR
