March 25, 2015 at 6:59 am
Hi
1. I need advice how can i merge one database to another database both databases having same schema /structure.
2. I need to load view data of one database into another database.
please help
March 25, 2015 at 7:26 am
How many tables ?
Do all tables have a primary key or some logic to match and compare ?
Redgate SQL Data compare does a pretty good job. Don't work for them but its the second time in a week I find myself recommending it.
March 25, 2015 at 7:31 am
Yes all the tables have a primary key. Actually both databases are same needs to merge db2 data to db1.
March 25, 2015 at 7:38 am
can you clarify when you say merge db2 data to db1.
Do you want to:
1) clear the data in db1 and copy all the data from db2
2) add to db1 any data in db2 thats not in db1
3) update db1 with data from db2 where keys match
If its a clear and repopulate then regular SQL Export/Import will do it, just be careful with dependencies or drop constraints first.
Is this a production database or something you can take offline while you do it ?
March 25, 2015 at 7:49 am
My requirement
2) add to db1 any data in db2 thats not in db1
Yes this is production database but please suggest if i go offline will the data mirroring from this production server to mirror server will effect?
March 25, 2015 at 9:42 am
Are both databases on same server?
If so then simple it should be straight forward to look for new rows and insert them. To guess at the impact we need to know:
How many tables ?
How many rows currently in the tables?
How many rows would you expect to find new each time it runs?
Downtime may not be needed but as with anything it depends.
March 25, 2015 at 11:38 am
Assuming you have a logical or natural primary key, I'd look to the MERGE statement as an easy, although not necessarily fastest, way to get this done. That assumes you can identify each row from each table independently. Otherwise, you could look to a third party tool like Red Gate SQL Data Compare[/url].
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 26, 2015 at 8:31 am
You could do this is SSIS, and you wouldn't have to rely on a linked server. Use a Foreach Loop Container to loop thru all of the tables, and pick the most appropriate method of doing the inserts/updates to the target database. It's easier to keep track of intermediate results in SSIS, doing fancy joins thru a linked server could have horrible performance.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply