0

I've built a script to migrate specific tables from one database to another and it's been working great- but I'd like to make it more efficient. It does a lot of needless work truncating and migrating the data where both the target and source database tables were already identical. I'm wondering if theres something like a hash function that could be run for an entire table on both the source and target databases to determine if everything is 100% identical between the two tables? I'm only caring about the data here, I don't need to check for schema differences.

Nathan
  • 435
  • 4
  • 16
  • You could do it at the record level, see https://stackoverflow.com/a/11186246/1260204 – Igor Aug 10 '21 at 21:13

2 Answers2

0

SQL Server ships with a utility for this: tablediff.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • I should have mentioned this in the top comment but there are many databases across multiple servers some with unique VPN requirements. The script acts as a bit of a proxy so that each database only needs to be able to talk to and from the machine running the script and not worry about communicating with each other. – Nathan Aug 10 '21 at 21:23
  • Then run `tablediff` from that central machine. It connects to a "source" and "destination" server to perform the comparison. – David Browne - Microsoft Aug 10 '21 at 21:29
  • True, I'll see what it takes to get this integrated, thank you – Nathan Aug 10 '21 at 21:49
0

If you're using Visual Studio there's a built in Data Comparison tool which you might find useful. To automate a migration script you could compare the source table to an empty table (with the same definition) and accept all changes.

In a SQL project within VS under Tools> SQL Server> New Data Comparison...

enter image description here

SteveC
  • 5,955
  • 2
  • 11
  • 24