How to overwrite tables in destination database

M_O

I'm a relatively new Redgate user, my agency switched from ApexSQL to Redgate earlier this year. One thing that I sometimes need to do is copy and overwrite some tables from our production environment down to test. I want to be able to use SQL Data Compare to do this because I only need to overwrite a about half of the tables in the test database, but it's still dozens of tables so it's messy to do manually.

Is there a way to use SQL Data Compare to copy and overwrite data from the source database to the destination database? I'm sure there must be a way to do this, but the documentation doesn't seem to cover this case and it's not obvious to me having come from a toolset where it was literally a checkbox.

I searched the forum for answers and found this question asked twice, but in both cases that answer was "yes you can" and that doesn't help me. I would like to know -how- to do it. 

Thank you.

JoshH

Hi, it's a fairly simple process to do what you need to do. 

After you choose your source, let's say production in this case, and the target, dev in this case, you click compare. 

You'll get something like this: 



On the left, there's the + sign. Expand that: 



From there you can tick the check boxes of the tables you don't want to overwrite. 



Then you can click deploy



That's it! You can use Data Compare to deploy or create a script for deployment, whichever you prefer. You can also choose to back up the target db, just in case anything goes wrong. 

M_O

That's the standard process for transferring rows. But looking at the script, it doesn't delete or truncate any of the tables, so any data that is already there will still be there after the transfer.

I could manually update the script but when there's 40+ tables and hundreds of thousands of rows of data, it's super unwieldy to do that. I was hoping that there was a built-in way to do it in the tool. As I said before, with the other toolset, there was just a checkbox to clear the tables before writing into them, which made this really easy to do. 

Does SQL Data Compare have an equally easy way to do this? If so, how do you do it?

JoshH

Ah, sorry for the misunderstanding. Data Compare does not have an automatic way of clearing the tables prior to deployment. 

Jessicabuckley219

First, set up a comparison project in SQL Data Compare, selecting your production database as the source and the test database as the target. After the comparison, review the results to ensure only the desired tables are included. In the deployment wizard, choose the synchronize option and review the SQL script that SQL Data Compare generates to confirm it will overwrite the data in the selected tables. Finally, execute the script to apply the changes. Make sure to back up the test database beforehand to avoid accidental data loss.