Professional SQL Server™ 2005 Programming

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