SQL SERVER SELECT INTO IDENTITY with BCP

Standard

A couple of things I learned recently:

1: If you carry out a SELECT column1, column2…. INTO dbo.NewTable the newly created table won’t have any of the indexes, foreign keys or primary keys etc. of the source/original table BUT!!! The table will retain any
IDENTITY specifications for any columns. So the source/original table I was looking at had an INTEGER IDENTITY column as the surrogate primary key. This was brought over to NewTable.

2: BCP will ignore the load for any columns where IDENTITY is specified. So when I was attempting to BCP data into NewTable all the columns came across as per source apart from the IDENTITY column. Which
had a seed of 1 and an increment of 1. Once I removed the IDENTITY specification the BCP worked out OK.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

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