asked on

SQL Merge Live & Test Databases

I have 2 SQL 2005 Databases...Call them Live & Test.

Basically I have worked on the Test database for the last week, adding columns & testing everything to get my tables right. As of right now the test database is truncated so it has no records at all.

What I'm looking to do is merge the 2 databases. I want to import the live data into the truncated test database so I don't have to recreate all of the tables.

The problem that I have so far is with the ID fields, because they are read-only fields I can't import them which means that all of my ID's are wrong if I import ignoring those fields.

I know there is a better way to do this I just don't know how I should go about this. Any help would be VERY appreciated!

Thanks.
Jon

Raja Jegan R

>> The problem that I have so far is with the ID fields, because they are read-only fields I can't import them which means that all of my ID's are wrong if I import ignoring those fields.

simple way to import from Live to Test database would be to use Import/Export wizard..
But since you have identity columns on those tables, you can use the below T-SQL:

If both databases are in different servers, then the below script will work only if you have created Linked server between those two servers.

set identity_insert schema_name.table_name on;

insert into testdb.schema_name.table_name ( columns_list)
SELECT columns_list
FROM livedb.schema_name.table_name

set identity_insert schema_name.table_name off;

Open in new window

Jon DeVito

ASKER

Thanks, both are on the same server.
Do I have to type out the column names in (column_list) or do i just pretty much copy & paste the code?

Thanks.
Jon

Raja Jegan R

Yes, that is one painful process..
You need to type in all column names present in those tables.

If you have some third party tools like Red Gate SQL Refactor, you can easily replace * with all columns list http://www.red-gate.com/products/sql-development/sql-prompt/

Jon DeVito

ASKER

Not really what I was looking for. Going that route I might as well just create them in the database.

That software looks nice but no way I can spend $1500 for the Developer Tools to merge databases.
Thanks though.

I would like to import the entire Live database into the test database overwriting all data in the test (which there is none). I wouldn't even care if it overwrites all columns already in the database.

Any other ideas on how to do this?

Thanks.
Jon

Raja Jegan R

>> I would like to import the entire Live database into the test database overwriting all data in the test (which there is none). I wouldn't even care if it overwrites all columns already in the database.

Then you can simply take a backup of your Live database and restore it in Test database..
Kindly let me know if you need help in taking backups and restoring it.

Jon DeVito

ASKER

I thought that doing it through the backup would overwrite my columns.

Raja Jegan R

I am not sure what you meant by Overwriting your columns.

Taking a backup and restoring it would have the replica of your live database with all objects structures retained as that of your Live database.
Since you don't have any data present in your test database, I think it should be the easier way to go to achieve your objective.

ASKER CERTIFIED SOLUTION

JoeNuvo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SQL Merge Live &amp; Test Databases

SQL Merge Live & Test Databases