By Lee Gould
Expert Author
Article Date: 2003-07-15
Question: Is it possible to input a text file into SQL Server using T-SQL?
Lee Gould's response:
If you are using SQL2000 then there is a new command available to you called BULK INSERT which basically performs the bcp command but within TSQL. Here is the basic command syntax. Typically, data importing is done via BCP or using DTS Data Transformation Services which can be found in Enterprise Manager. DTS is far more flexible than the bcp/bulk insert command and depending on the type of data you are trying to import you may want to consider that as an alternative. The advantage behind this method is it allows you to use workflow and job scheduling for operationalizing.
Anyway, hope this helps you ...
Lee
BULK INSERT [ [ 'database_name'.] [ 'owner' ].] { 'table_name' FROM 'data_file' }
[ WITH
(
[ BATCHSIZE [ = batch_size ] ]
[ [ , ] CHECK_CONSTRAINTS ]
[ [ , ] CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ] ]
[ [ , ] DATAFILETYPE [ =
{ 'char' | 'native'| 'widechar' | 'widenative' } ] ]
[ [ , ] FIELDTERMINATOR [ = 'field_terminator' ] ]
[ [ , ] FIRSTROW [ = first_row ] ]
[ [ , ] FIRE_TRIGGERS ]
[ [ , ] FORMATFILE = 'format_file_path' ]
[ [ , ] KEEPIDENTITY ]
[ [ , ] KEEPNULLS ]
[ [ , ] KILOBYTES_PER_BATCH [ = kilobytes_per_batch ] ]
[ [ , ] LASTROW [ = last_row ] ]
[ [ , ] MAXERRORS [ = max_errors ] ]
[ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
[ [ , ] ROWS_PER_BATCH [ = rows_per_batch ] ]
[ [ , ] ROWTERMINATOR [ = 'row_terminator' ] ]
[ [ , ] TABLOCK ]
)
]
