Make one table equal to another without deleting *

Question

I know this is a little strange, but if someone helped, it would be very helpful.

The scenario is that we have a production database on 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, they launch the C # application and create a series of .sql scripts that we can execute on the remote side (essentially delete *, insert), but we are looking for something a little more complex. since downtime from removal * is unacceptable. This is all reference data that controls menu items, functionality, etc. The main website.

I have a sproc that essentially returns the diff of two tables. My thinking is that I can insert all the expected data into the tmp table, run diff and delete something from the destination table that is not in the source code, and then do the rest.

The question is, is there an easy way to do this without using a cursor? To illustrate, sproc returns a recordset, structured as follows:

TableName Col1 Col2 Col3 Dest Src

Everything that is contained in the recordset with TableName = Dest must be deleted (since it does not exist in src), and anything in Src must be added to dest. I can't figure out how to do this purely on a set basis, but my DB-fu is weak.

Any help would be greatly appreciated. Sorry if the explanation is sketchy; let me know if you need more details.

Answer 1

, sproc . FULL JOIN , . SQL . .

---

, , , LEFT RIGHT JOINS. NotePad, , :

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' 

---

, , , . 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

DML, .

Answer 2

, , SQL Server 2008: MERGE.

, :

MERGE DestinationTable d
USING SourceTable s
    ON d.Id = s.Id
WHEN MATCHED THEN UPDATE
    SET d.Col1 = s.Col1, d.Col2 = s.Col2, ...
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Id, Col1, Col2, ...)
    VALUES (s.Id, s.Col1, s.Col2, ...)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

. DestinationTable SourceTable.

Answer 3

tablediff

. -f, t-sql, :

Transact-SQL script . Transact-SQL script. _ , Transact-SQL script , .

Answer 4

? ddl , , .

: , , , db , .

. sqlcmd osql .