• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

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
0
Jon DeVito
Asked:
Jon DeVito
  • 4
  • 3
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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

0
 
Jon DeVitoAuthor Commented:
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
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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/
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
Jon DeVitoAuthor Commented:
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
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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.
0
 
Jon DeVitoAuthor Commented:
I thought that doing it through the backup would overwrite my columns.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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.
0
 
JoeNuvoCommented:
In case you want to using SQL Server Import and Export Wizard
(using Microsoft SQL Server Management Studio -> right click at database name -> select Import Data or Export Data)

follow the step to choose Source/Destination server/database
and choose to Copy data fro one or more tables or views
when select the table, click Edit Mappings and choose Enable identity insert

that would do the job for you.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now