Tuesday, May 11, 2010

T-SQL script for BULK INSERT

Here is the T-SQL code to BULK insert data from text file to SQL Table:

BULK INSERT [Database]..[TableName]

FROM 'D:\test.txt' -- Path of text file
WITH
(
   FIRSTROW = 1
   ,BATCHSIZE = 10000
   ,FIELDTERMINATOR = ','
   ,ROWTERMINATOR = '\n'
   ,LASTROW = 20
)
GO

Here is the description of Keywords used:

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

BATCHSIZE
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.

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

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

LASTROW
Specifies the number of the last row to load. The default is 0, which indicates the last row in the specified data file.

No comments:

Post a Comment

Here are few FREE resources you may find helpful.