July 10, 2008 at 1:20 pm
How do I "append" the data from 1 database to another--both had changed thru inserting new data and are currently on different servers. Now I need to add database2 data to database1 data (both have same name) and am retiring the server that database2 is on. I can't seem to find how to append, and import/export didn't work for my test that I tried.
Any help greatly appreciated! Thanks
July 10, 2008 at 2:21 pm
The first thing you need to be able to do is identify the data to be appended. But, what about existing data that has been changed in database1? Can it be changed - and if so, what do you want to do with that information?
Based upon the above, there are several ways you can approach this.
1) Use linked servers
a) Create a query on database 2 to extract the rows to be inserted from database 1 on the linked server.
b) Insert the rows selected to the destination table in database 2.
2) Use SSIS - same concept as above, but doesn't require using linked servers
3) Backup/Restore
a) If the most current version is database 1, but you need that as the new version of database 2, backup and restore database 1 as database 2.
Now, if you need to update database2 with changes to existing rows - you need to be able to identify the rows that have changed and then use an update statement.
Please read the following article:
Best Practices: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 10, 2008 at 2:50 pm
Thanks for the reply! The most current data resides on database2 but database1 has data on it that database2 does not! And I am not sure how much new data is on database2 but possibly a lot. What happened was that in moving to a new server (64 bit) I detached and reattached successfully a bunch of databases. The previous admin didn't want 3 databases moved since he was adding data to them as I did the other task. But he had moved them anyway to the new server, so some data got added before I started my task. Then he decided this was not working and so went back to adding the data on the old server. Not so smart. He should have detached and reattached or anything other than just stop using the databases on the new server and start using them on the old again. Anyway, so new data on new server copy(database1), and new data on old server copy(database2) with the old server having the more recent and larger amount of new data. Hope that makes sense.
BTW, I have not been able to figure out SSIS--where do I get that going, if that is the answer? All I can find help for is DTS and I believe that is an older version of SQL Server util.
Thanks again.
July 10, 2008 at 8:03 pm
Well, I don't envy you this task - it is not going to be easy. Any way you can convince the user that the old database should be moved and accept the loss of data?
As for SSIS - you can review quite a few articles on this site, as well as http://www.sqlis.com.
Since this appears to be a one time job, I would use a linked server and create update/insert queries to update the data in the new database.
Good luck
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 11, 2008 at 6:23 am
So no "append" in SQL Server? I am more of a PostgreSQL or MySQL person and there is append in those...this seems like it should be easier. I am not at all familiar with linked servers--gulp!
July 11, 2008 at 7:09 am
As Jeffrey stated, there is an "append" available but it comes with a potential cost. If inserting from DB2 to DB1, can you accept potential duplicates in the tables? Can SQL even allow it (primary key constraints)? Order of inserts will be key due to potential foreign key constraints. You cannot simply say "insert all" and expect it to work perfectly. There is work, from your end, involved. Whatever methods you try, be sure you have a current full backup BEFORE starting.
And linked servers are fairly straight-forward, nothing to get nervous about! 😛
-- You can't be late until you show up.
July 11, 2008 at 7:20 am
I guess I had better check out the info on linked servers! I will post again once I get a little info under my belt. I am also thinking I may be able to tolerate some duplicates with the append method, if that ends up being the way to go. Then I can just manually delete them, if there are not too many, or write a query to get rid of them? I will check out the linked server info right now. Thank you very much for the replies!
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply