Making one table equal to another without a delete *
- by Joshua Atkins
Hey,
I know this is bit of a strange one but if anyone had any help that would be greatly appreciated.
The scenario is that we have a production database at a remote site and a developer database in our local office. Developers make changes directly to the developer db and as part of the deployment process a C# application runs and produces a series of .sql scripts that we can execute on the remote side (essentially delete *, insert) but we are looking for something a bit more elaborate as the downtime from the delete * is unacceptable. This is all reference data that controls menu items, functionality etc of a major website.
I have a sproc that essentially returns a diff of two tables. My thinking is that I can insert all the expected data in to a tmp table, execute the diff, and drop anything from the destination table that is not in the source and then upsert everything else.
The question is that is there an easy way to do this without using a cursor? To illustrate the sproc returns a recordset structured like this:
TableName Col1 Col2 Col3
Dest
Src
Anything in the recordset with TableName = Dest should be deleted (as it does not exist in src) and anything in Src should be upserted in to dest. I cannot think of a way to do this purely set based but my DB-fu is weak.
Any help would be appreciated. Apologies if the explanation is sketchy; let me know if you need anymore details.
