Microsoft® SQL Server® 2012 Bible

428

Part IV: Programming with T-SQL

BULK INSERT handles columns in the order they appear in the source comma-delimited fi le, and the columns must

be in the same order in the receiving SQL table. Bulk inserting into a view provides a data abstraction layer so that

any changes in column order won’t break the BULK INSERT code.

When developing a BULK INSERT statement, it’s generally useful to open the source fi le

using Excel and examine the data. Excel often reformats data, so it’s best not to save fi les

in Excel. Sorting the data by the columns can help fi nd data formatting anomalies.

BCP

Bcp, short for bulk copy program, is a command-line variation of bulk operations. Bcp dif-

fers from BULK INSERT in that it is command-line executed and can import or export data.

It uses many of the same options as BULK INSERT. The basic syntax is as follows:

BCP destination table direction datafile options

For the destination, use the server name along with the complete three-part name (server

and database.schema.object). For a complete listing of the syntax, just type bcp at the

command prompt.

Because this is an external program, it needs authorization to connect to SQL Server. You

have two options: Use the -P password option and hard-code your password into the batch

fi le script, or omit the -P, in which case it prompts for a password. Neither is a good option.

You can also use integrated security, which is usually considered the best practice.

For straightforward ETL operations, use T-SQL and BULK INSERT. For complex ETL loads, Integration Services is

great. You might have little use for automating ETL processes using DOS batch scripts and bcp, although PowerShell

may be an option to consider.

Summary

T-SQL extends the SQL query with a set of procedural commands. Although it’s not the most

advanced programming language, T-SQL gets the job done. You can use T-SQL batch com-

mands in expressions or packaged as stored procedures, user-defi ned functions, or triggers.

Following are a few key points to remember from this chapter:

■ (^) The batch terminator, GO, is only a Query Editor command, and it can send the
batch multiple times when followed by a number.
■ (^) Ctrl+K+C converts the current lines to comments, and Ctrl+K+U uncomments the lines.
c16.indd 428c16.indd 428 7/30/2012 5:38:13 PM7/30/2012 5:38:13 PM
http://www.it-ebooks.info