Bulk Insert works in most cases but if your operation is based on sequence, you can't rely on bulk insert as it loads the data from the file regardless of sequence. we had quiet a few issues with this.
If you set the BATCHSIZE to 1 the importation of data may be slower than with a larger setting but it will be inserted into your table in the same order in which it is read from the data file. You can also use a VIEW that selects all columns except the IDENTITY column from a staging table to act as a façade for BULK INSERT.
Open the attached zip file and save the 32MB text file inside as C:\@\bi.tbl. The first fours lines of the text file are below and are repeated 980,514 times making a file with ~39 million lines in it. You'll see in the code sample below where I check the identity assigned to each line to validate the file was inserted in order.
IF EXISTS ( SELECT *
WHERE object_id = OBJECT_ID(N'dbo.bi')
AND type IN (N'U') )
DROP TABLE dbo.bi;
CREATE TABLE dbo.bi (line_number INT NOT NULL IDENTITY(1,1), name VARCHAR(100), age INT);
IF EXISTS ( SELECT *
WHERE object_id = OBJECT_ID(N'dbo.bi_facade') )
DROP VIEW dbo.bi_facade;
CREATE VIEW dbo.bi_facade AS SELECT name, age FROM dbo.bi;
BULK INSERT YourDatabaseName.dbo.bi_facade
-- should return 0 rows if lines in the file were inserted into the table in order
SELECT TOP 10
name = 'Joe'
AND line_number % 4 != 1
name = 'John'
AND line_number % 4 != 2
name = 'Mary'
AND line_number % 4 != 3
name = 'Jane'
AND line_number % 4 != 0
Note: the script consistently takes about 15 seconds to run on my home machine.
As I mentioned in an earlier post, T-SQL's BULK INSERT, the SQL Server command line tool bcp.exe, the .NET Base Class Library class System.Data.SqlClient.SqlBulkCopy and SSIS's OLE DB Destination Component all connect to SQL Server's BulkCopy API so it would stand to reason that a batch size option would exposed through all of these tools, and it is. In BULK INSERT it is BATCHSIZE. In bcp.exe it is the -b option. In SqlBulkCopy it is the BatchSize property, and in SSIS's OLE DB Destination Component it is the Fast Load Option FastLoadMaxInsertCommitSize.
There are no special teachers of virtue, because virtue is taught by the whole community.