Microsoft® SQL Server® 2012 Bible

426

Part IV: Programming with T-SQL

To test the BULK INSERT command, use the Address.csv fi le that’s part of the build

script to load the AdventureWorks2012 sample database. You can fi nd it at http://

msftdbprodsamples.codeplex.com/releases/view/55330 and it is called

AdventureWorks 2012 OLTP Script.

The following batch bulk inserts from the Address.csv fi le in the

AdventureWorks OLTP directory into the AWAddress table:

Use Tempdb;

DROP TABLE AWAddressStaging;

CREATE TABLE AWAddressStaging (

ID INT,

Address NVARCHAR(500),

City NVARCHAR(500),

Region NVARCHAR(500),

PostalCode NVARCHAR(500),

SpatialLocation nvarchar(500),

GUID NVARCHAR(500),

Updated DATETIME

);

BULK INSERT AWAddressStaging

FROM

'C:\Program Files\Microsoft SQL Server\110\Tools\Samples

\AdventureWorks OLTP\Address.csv'

WITH (FIRSTROW = 1,ROWTERMINATOR ='\n');

The fi rst thing to understand about BULK INSERT is that every column from the source

table is simply inserted directly into the destination table using a one-to-one mapping. The

fi rst column from the source fi le is dumped into the fi rst column of the destination table.

Each column lines up. If there are too many columns in the destination table, then it will

fail. However, if there are not enough columns in the destination table, then BULK INSERT

will work, as the extra data is placed into the bit bucket and simply discarded.

Best Practice

Because BULK INSERT is dependent on the column position of both the source fi le and the destination

table, it is best practice to use a view as an abstraction layer between the BULK INSERT command and

the table. If the structure of either the source fi le or the destination table is altered, then modifying

the view can keep the BULK INSERT running without having to change the other object’s structure.

Another best practice is to BULK INSERT the data into a staging table, check the data, and then per-

form the rest of the transformations as you merge the data into the permanent tables. As long as you

don’t mind copying the data twice, this works well.

c16.indd 426c16.indd 426 7/30/2012 5:38:13 PM7/30/2012 5:38:13 PM

http://www.it-ebooks.info