T-SQL script for BULK INSERTHere 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 fileWITH( 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.
BATCHSIZESpecifies 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.
FIELDTERMINATORSpecifies the field terminator to be used for char and widechar data files. The default field terminator is \t.
ROWTERMINATORSpecifies the row terminator to be used for char and widechar data files. The default row terminator is \r\n (newline character).
LASTROWSpecifies the number of the last row to load. The default is 0, which indicates the last row in the specified data file.
x
BULK INSERT [Database]..[TableName]
FROM 'D:\test.txt' -- Path of text fileWITH( 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.
BATCHSIZESpecifies 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.
FIELDTERMINATORSpecifies the field terminator to be used for char and widechar data files. The default field terminator is \t.
ROWTERMINATORSpecifies the row terminator to be used for char and widechar data files. The default row terminator is \r\n (newline character).
LASTROWSpecifies the number of the last row to load. The default is 0, which indicates the last row in the specified data file.
x