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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.