18.3. BULK INSERT
One of the "cousins" that I mentioned at the beginning of the chapter was the BULK INSERT command. In order to make use of this command, you must be a member of either the sysadmin or bulkadminserver role.
What BULK INSERT does is essentially operate like a limited version of BCP that is available directly within T-SQL. The syntax looks like this:
BULK INSERT [['<database name>'.]['<schema name>'].]'<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 [= <no. of kilobytes>]] [, LASTROW [ = <last row no.>]] [, MAXERRORS [ = <max errors>]] [, ORDER ({column [ASC|DESC]} [ ,...n ] )] [, ROWS_PER_BATCH [= <rows per batch>]] [, ROWTERMINATOR [ = '<row terminator>']] [, TABLOCK] [, ERRORFILE = '<file name>'] ) ]
Now, if you are getting a sense of déjà vu, then you're on top of things for sure — these switches pretty much all have equivalents in the basic BCP import syntax that we started off the chapter with.
The special permission requirements of BULK INSERT are something of a hassle (not everyone belongs to sysadmin or bulkinsert), but BULK INSERT does carry with it a couple of distinct advantages:
It can be enlisted as part of a user-defined ...
Get Professional SQL Server™ 2005 Programming now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.