February 5, 2008 at 5:39 am
Does anyone know how to transfer a table from one server to another and preserve the identity column definition and hence the next identity value for that column.
Have played about with IDENTITY_INSERT, but you have to use the 'VALUES' clause in the INSERT statement, and I want to use select * from to transfer the data.
February 5, 2008 at 8:48 am
It should be easy. Assume the table you are copying to/from has 4 cols: id (identity), col1, col2, col3.
Something similar to this should get you where you need to go:
---------
SET IDENTITY_INSERT ON
INSERT INTO (id, col1, col2, col3)
SELECT id, col1, col2, col3 FROM
SET IDENTITY_INSERT OFF
The Redneck DBA
February 5, 2008 at 8:58 am
Thanks Jason, Yes I had come to the same conclusion - eventually...
It's the 'select * from' that it does not like - you have to explicitly specify all columns in the insert and select, i.e.
SET IDENTITY_INSERT test ON
insert into test (col1, col2, col3) SELECT col1, col2, col3 from old_test
NOT...
SET IDENTITY_INSERT test ON
insert into test SELECT * from old_test
or
SET IDENTITY_INSERT test ON
insert into test (col1, col2, col3) SELECT * from old_test
February 5, 2008 at 9:18 am
One other thing I would consider is when you set up your identity column specify the initial value as the "next" value from your orriginal table. That way if someone else does an insert between the time you create the table and the time you managed to load it the new identity column will still be correct.
I had a high transaction system where this happend to me 🙂
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply