SQL Server – BulkCopy (BCP) – Identity Columns

Background

I am copying data from Production to my personal Development box, but my SQL Join Queries are not yielding any data.

Code

Original Code


SETLOCAL

 set DBTargetServer=.\SQLEXPRESS_V2014
 set DBTargetDB=Dev
 set BATCH_SIZE=30000

 sqlcmd -e -b -Q"truncate table dbo.tblStaffingDateTime" -S%DBTargetServer% -d%DBTargetDB%

 timer.exe

   bcp %DBTargetDB%.dbo.tblStaffingDateTime in %TEMP%\dbo.tblStaffingDateTime.data -T -n -b%BATCH_SIZE% -S%DBTargetServer%

 timer.exe /S

ENDLOCAL 

 

Revised Code

Traced the problem back to ensuring that we preserve our Identity Values.

When issuing bcp, we use -E to insist that our supplied identify values are used, rather than for the system to generate its own.


SETLOCAL

 set DBTargetServer=.\SQLEXPRESS_V2014
 set DBTargetDB=Dev
 set BATCH_SIZE=30000

 sqlcmd -e -b -Q"truncate table dbo.tblStaffingDateTime" -S%DBTargetServer% -d%DBTargetDB%

 timer.exe

   rem -E Keep Identity Values ON
   bcp %DBTargetDB%.dbo.tblStaffingDateTime in %TEMP%\dbo.tblStaffingDateTime.data -T -n -E -b%BATCH_SIZE% -S%DBTargetServer%

 timer.exe /S

ENDLOCAL 

Metrics

Using Gammadyne Timer Utility ( http://www.gammadyne.com/cmdline.htm#timer ) compared how long it takes to insert 150 thousand records.

 -E Skipped :- Does not Preserve Identity Values
keepIdentityOffCleaned

 -E Added :- Preserve Identity Values

keepIdentityOnCleaned

 

Conclusively, when we preserve our Identity Column our throughout is about 15% less.

Dedicated

Will like to go another way with this.  But, got to claim my own.  And, this morning it is Duke Blue Devils,  the National Collegiate Basketball Champion 2014.

But, even then it is April.  And, yet still considered going another road, Dave Loggins’ Pieces of April.

But, will rest with Touch-Of-Pennsylvania ( https://www.youtube.com/watch?v=9_sQv2hfVxg )

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s