Migrating SQL Server database

I have 2 SQL Server databases A and B that are identical except that in B,  one of the tables has a new column. I want to copy everything from A to B, with the new column having the default value.

Here's what I've tried. First, I disabled constraints. I don't want to be concerned about constraint violations while copying. Then for each table T, I did
DELETE FROM B.T
INSERT INTO B.T
SELECT * FROM A.T

Open in new window

This doesn't work if T has an identity column. I tried
SET IDENTITY_INSERT B.T OFF
DELETE FROM B.T
INSERT INTO B.T
SELECT * FROM A.T
SET IDENTITY_INSERT B.T ON

Open in new window

However, this doesn't work either; it requires that I explicitly list the columns. I can do that, of course, but it's tedious and error-prone. Almost every table has an identity column. Is there some better way to do this?
LVL 1
BlearyEyeAsked:
Who is Participating?
 
Steve WalesSenior Database AdministratorCommented:
Wow, I completely missed them in your second code block, sorry about that.

Unfortunately I don't have an easy solution for that.  You should be able to dynamically build an insert statement using syscolumns.  Unfortunately  I don't have time to work that out for you at the moment, so I had a little chat to Mr. Google.

I found an article on sqlservercentral.com (which i hope isn't violating any rules linking here).

http://www.sqlservercentral.com/scripts/IDENTITY+Property/90885/

The author indicates that using this code, you should be able to provide a table name and get an insert statement build for you.  Using sp_MSForEachTable he even gives you a way to generate code for all tables in a database.

Hopefully, at least, that's enough to get you walking down the right path for a solution.
0
 
Steve WalesSenior Database AdministratorCommented:
You can temporarily disable the auto increment of an identity column if you want to insert your own values as a part of a data migration.

Check out SET IDENTITY_INSERT: https://msdn.microsoft.com/en-us/library/ms188059.aspx

Set it to ON to insert your own values, then turn it off when you're done - so your first solution should work doing that.

It is for SQL Server 2008 and higher though (so if you're on 2005, this won't work).
0
 
BlearyEyeAuthor Commented:
Oops. In my original post I got the IDENTITY_INSERT statements backwards (it's right in the code I'm testing). It should have been
SET IDENTITY_INSERT B.T ON
DELETE FROM B.T
INSERT INTO B.T
SELECT * FROM A.T
SET IDENTITY_INSERT B.T OFF

Open in new window

My problem still remains: how to avoid explicitly listing the columns.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott PletcherSenior DBACommented:
Can you just restore A to B and alter the B table to include the new column?  You might need to sync up userids with logins depending on the details of your db setup.
0
 
BlearyEyeAuthor Commented:
@ScottPletcher: I considered this but want to avoid the extra (and easy to forget) step of modifying the SQL Server script if the properties of the column change. Still, it's a possibility.
0
 
Scott PletcherSenior DBACommented:
You could run a schema comparison at time of restore to find any differences, and dynamically create the code to sync them.  On just the schemas, that wouldn't take long.
0
 
BlearyEyeAuthor Commented:
Scott, any pointer on how to dynamically compare the differences & create the necessary code?
0
 
Scott PletcherSenior DBACommented:
I tend to stick to tablediff, since it's provided by MS and thus I expect it to remain accurate even through different SQL editions.  It's a command line tool, so like sqlcmd it can be a pain to get working initially.  But it's actually pretty fast.

There's also open-source software named "Open DBDiff" that (says it) does this; I haven't used it, but it gets generally good reviews.
0
 
BlearyEyeAuthor Commented:
I have SQL Server Express 2008 and apparently tablediff doesn't come with it. So far I haven't been able to find it on my system or where to download it.
0
 
Scott PletcherSenior DBACommented:
Check something along these lines:
C:\Program Files (x86)\Microsoft SQL Server\100\COM\tablediff.exe

Hmm, tablediff is normally used internally in SQL Server for replication.  Since Express can only be a replication subscriber, maybe it's not in it.

It might be in the Developer Edition.  Or maybe someone you know has a standard or enterprise edition and can give you a copy of the tablediff.exe file.
0
 
BlearyEyeAuthor Commented:
I think it's not included. Since I'm working on a project by myself, I unfortunately don't know anyone that could give me a copy.

Meantime, I'm trying Steve Wales' suggestion. The script linked to is complex but seems to work and I can probably leverage it.
0
 
BlearyEyeAuthor Commented:
I tried spd_Build_Insert_Into_Stmt_With_Identity that Steve suggested. I had to tweak the output some--change TRUNCATE TABLE to DELETE FROM since a table with a foreign constraint can't be truncated was the biggest one--but it seems to basically work and satisfy my requirement.

Both suggestions were plausible so will be giving credit to both. I couldn't try Scott's of course since I don't have tablediff.
0
 
BlearyEyeAuthor Commented:
Great help, thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.