SELECT INTO

Question

SELECT INTO

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

sunny.tjk (9/12/2011)
sqlbuddy123 (9/11/2011)
Jeff got it perfectly right. Just use CREATE Table and add an identity column to it.
Thank You,
Best Regards,
SQLBuddy
I'm still confused if I should choose this approach because I think doing a cross server will hurt the performance.
i.e. SELECT * INTO destination table
FROM remoteserver.dbname.sourcetablename.

Doing ANYTHING across servers will hurt performance. 😉

If you really want this to be fast, do what SQL Server does when replication first starts on a table... do a BCP OUT, Create the table on the remote server, and do a BULK INSERT to load it. Depending on the table width, estimate between 1 and 5 million rows per minute in each direction IF you following "Minimal Logging Rules" on the destination table and, of course, depending on how wide the table is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

sqlbuddy123 (9/11/2011)
Jeff got it perfectly right. Just use CREATE Table and add an identity column to it.
Thank You,
Best Regards,
SQLBuddy

I forgot we're migrating between servers. A straight SELECT/INTO would look attractive but it won't maintain IDENTITY column properties across servers. BCP and BULK INSERT might be the way to go here.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

sunny.tjk (9/12/2011)
sqlbuddy123 (9/11/2011)
Jeff got it perfectly right. Just use CREATE Table and add an identity column to it.
Thank You,
Best Regards,
SQLBuddy
I'm still confused if I should choose this approach because I think doing a cross server will hurt the performance.
i.e. SELECT * INTO destination table
FROM remoteserver.dbname.sourcetablename.

But you don't really know at this point. Why not do a test right now and see how long it takes to do the TOP 10 million rows?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

With the SELECT INTO not only will it not carry over the Identity but it will not carry over Indexes, constraints, etc.

As was suggested Script the Table and perform an INSERT INTO.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 5

Jeff Moden (9/12/2011)
sunny.tjk (9/12/2011)
sqlbuddy123 (9/11/2011)
Jeff got it perfectly right. Just use CREATE Table and add an identity column to it.
Thank You,
Best Regards,
SQLBuddy
I'm still confused if I should choose this approach because I think doing a cross server will hurt the performance.
i.e. SELECT * INTO destination table
FROM remoteserver.dbname.sourcetablename.
Doing ANYTHING across servers will hurt performance. 😉
If you really want this to be fast, do what SQL Server does when replication first starts on a table... do a BCP OUT, Create the table on the remote server, and do a BULK INSERT to load it. Depending on the table width, estimate between 1 and 5 million rows per minute in each direction IF you following "Minimal Logging Rules" on the destination table and, of course, depending on how wide the table is.

If we've to place the file in a shared storage location, I'm concerned if it might take lot of time doing a BCP out and then BCP in. We have 15 tables which have 700 million rows on an average to be migrated. I guess we might get 3 day maintenance window.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

BCP OUT to the local Server.

Compress the file and copy to the destination server.

Uncompress the file.

BCP IN to the destination server.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 7

Welsh Corgi (9/13/2011)
BCP OUT to the local Server.
Compress the file and copy to the destination server.
Uncompress the file.
BCP IN to the destination server.

Thanks Welsh.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 8

There are some restrictions but consider using BCP Native mode to get the best performance when transferring data between SQL Servers.

http://msdn.microsoft.com/en-us/library/ms191232.aspx

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato