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 ]
)
]
Lee Gould , President of Rocketrainer (http://www.rocketrainer.com) a revolutionary web based athletic coaching ASP. Lee does database architecture and development consulting for Oracle, Sybase and Microsoft SQL Server. Lee is a co-author of Transact SQL Programming published by O’Reilly and has been a database professional for over 14 years, working on Wall Street for 7 years. Lee holds an MBA from Seton Hall University.
