October 13, 2008 at 9:27 am
I'm having trouble figuring out how to migrate table data using just a SELECT * FROM query, as the identity columns in several tables exist as foreign keys in other tables, and reproducing the data relationships would be extremely difficult. Can someone suggest the best way to get the tables from one SQL 2K5 server to another without using SSIS ?It's not available to me, so I just don't have that option.
I've tried turning IDENTITY INSERT ON, but I don't appear to be able to do that from the remote side, and it tells me I'm not allowed to write to that identity column (from the remote query window) if I turn it on local to the destination server. Perhaps I simply need a better set of steps to follow?
Thanks in advance for any/all assistance.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 13, 2008 at 4:29 pm
I generally use BCP.
I do this quite a lot migrating client databases.
October 14, 2008 at 6:42 am
Well, to solve my problem, here are the steps I took, as the use of BCP simply wasn't available to me on either of the servers.
1.) Script out the CREATE TABLE statements, and then strip out all the constraints
2.) Modify those scripts to eliminate the IDENTITY(1,1) pieces.
3.) Modify the table name to contain 'A_' at the beginning of the table name
4.) Run the modified scripts on the new server to create these slightly modified versions of the tables.
5.) Use the linked server on the "old server" to INSERT INTO them using SELECT with a column list for all columns
6.) Turn IDENTITY_INSERT ON for these tables on the new server
7.) INSERT INTO NEW_TABLE SELECT * FROM MODIFIED_TABLE
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply