0

I have two distinct mssql databases with identical schemas but different data on one system. I want to merge the data between the two databases by adding the data from one into the other.

I tried to use Redgate Data Compare but couldn't find any instructions how to do it.

Are there any tools (or databases that have built-in tools) or instructions to make this job fairly simple?

Cameroon P
  • 119
  • 3
  • 10
  • You should do it using the integration service (SSIS) your requirements are straight forward and it will be easy even if you are using it for the first time. but you might need to figure out what a few issues related to what you are trying to do (same IDs present in both databases for example) – Gubr Apr 09 '16 at 09:26
  • I googled it and couldn't find any instructions. Do you have one? –  Cameroon P Apr 09 '16 at 10:23
  • Does each table has an ID column? What do you do if there are identical rows or IDs in each database? Do you update the original or choose one over the another or keep both? – DhruvJoshi Apr 09 '16 at 11:13
  • this link : https://www.youtube.com/watch?v=FUQeTjd8jVI&nohtml5=False or this one: https://technet.microsoft.com/en-us/library/ms169917(v=sql.110).aspx – Gubr Apr 09 '16 at 17:51
  • You didn't explain how Redgate's SQL Data Compare didn't work for you. – David Atkinson Apr 09 '16 at 19:13
  • Instead of using Redgate I used query method. –  Cameroon P Apr 11 '16 at 20:04

1 Answers1

1


xSQL Software has developed a set of tools for this type of database operations. You can use xSQL Data Compare. To achieve the result you require you specify the left and right database, do the compare and then, to add the data that are missing from the left db to the right db you need to uncheck sync for the right rows and generate and execute the script.
Here you can see the rows unchecked: enter image description here

And as you can see, the script does not generate any DELETE statements, only INSERTS for the rows that are missing on the right DB
enter image description here
Hope this helps!
Disclosure: I'm affiliated to xSQL Software

endizhupani
  • 3
  • 2
Endi Zhupani
  • 736
  • 2
  • 7
  • 22