Making one table equal to another without a delete *

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.

Solution

Yeah, that sproc would work. Use a FULL JOIN with that table and add a column to indicate insert, update or delete. Then create separate SQL statements for them based on the column indicator. Set based.

Sorry not a FULL JOIN, you'll need to break them down to separate LEFT and RIGHT JOINS. Did this in NotePad, so apologies if it doesn't work:

INSERT INTO tempDeployData(ID,IUDType)
SELECT ed.id, 'D'
FROM    tmpDeployData td
    RIGHT JOIN existingData ed ON td.id = ed.id
WHERE td.id IS NULL     


UPDATE td
SET td.IUDType = CASE WHEN ed.id IS NULL THEN
                         'I'
                         ELSE
                         'U'
                         END
FROM    tmpDeployData td
    LEFT JOIN existingData ed ON td.id = ed.id


INSERT INTO existingData(ID,a,b,c)
SELECT td.ID,td.a,td.b,td.c
FROM tmpDeployData td
WHERE td.IUDType = 'I'

DELETE ed
FROM existingData ed
    INNER JOIN tmpDeployData td ON ed.ID = td.ID
WHERE td.IUDType = 'D'

UPDATE  ed
SET     ed.a = td.a,
        ed.b = td.b,
        ed.c = td.c
FROM existingData ed
INNER JOIN tmpDeployData td ON ed.ID = td.ID
WHERE td.IUDType = 'U'

Just realized you're pulling info into the temptable as a staging table, not the source of the data. In that case you can use the FULL JOIN:

INSERT INTO tmpDeployData(ID,a,b,c,IUDType)
SELECT  sd.ID, 
        sd.a, 
        sd.b, 
        sd.c
        'IUDType' = CASE WHEN ed.id IS NULL THEN
                         'I'
                         WHEN sd.id IS NULL THEN
                         'D'
                         ELSE
                         'U'
                         END
FROM    sourceData sd
    FULL JOIN existingData ed ON sd.id = ed.id

Then same DML statements as before.