Best way to copy tables without a primary key or unique constraint? Looking into synchronizing tables in a database across multiple servers using a DML method like MERGE, only to realize that some of the largest tables that take up the majority of the total size of the database do not have a primary key or unique constraint and therefore have duplicate data. As a result, doing a MERGE results in a full table scan, which explodes tempdb. For business reasons, I cannot alter the table structure or data itself.
Is there a way to copy these tables on a routine basis other than backing up and restoring the entire database?
AMtwo's point is without a primary key defined you have no reliable way to consolidate a record between a table on both servers. A primary key is the only way to guarantee row 137 is the same exact row 137 you're comparing it against between server 1 and server 2.
The closest thing you can attempt is use the HASHBYTES() function with a minimal collision hashing algorithm to hash the entire row, and then compare hashes. You can even goes as far as creating a computed column or an indexed view so you can index the hashed value and do quicker comparisons. The downside to this is with no primary key on the table means it's entirely possible two rows have exactly the same values across all columns within the same table and result in a duplicative join when comparing across servers resulting in excessive row comparisons.
The better solution from an accuracy standpoint would be to use a feature like Snapshot Replication on an appropriate schedule (e.g. hourly if that synchronization timeframe is acceptable but you can theoretically schedule it to run as soon as every second - though I wouldn't recommend this frequently). This essentially dumps the table on the target server and re-inserts all the rows from the source server, only for the tables you choose to replicate. You may want to even consider using a hybrid replication solution by implementing Transactional Replication for your tables that do have primary keys which offers near realtime synchronization.