Update existing and insert new in SSIS 2008r2

Question

I have two backup databases with 12 months data in one and 10 months data in another.. because of some reasons i can't use 12 months DB. So i need to update 10 months DB with 12 months data(new rows, new entities etc and update existing records) should be added to 10 months DB).. can anyone help me on this..

Answer 1

I'm assuming these databases are on separate SQL instances & not on the same one??

I'd use the SSIS data Export wizard (right click Database in Mgmt Studio -> Tasks -> Export Data) to copy all the 10 month tables across to a database in the same instance as the 12 month DB.

I'd then backup all the data in the 12 month tables in case something went wrong (SELECT INTO works well here)

Finally use T-SQL MERGE to push the 10 month data into the 12 month database.

Or something like that anyway :-) I'm not 100% clear on your full requirements but you get the gist...

Answer 2

If I understand your question correctly: Here are list of things you can do: 1) you can first get a back of 10 month database then add a field called Is12Month as bit 2) After that compare the data and write a update statement based on Is12Month.