how to add table record to another table(With update & delete)-CodePudding

I created two tables called table1 and table2.

Columns of table1 are Id, name, email

Columns of table2 are Id, name, email (same columns for both table1 and table2).

I want to create a query to add table1 records to table2.

Then I update or delete the table1's data, then I run again same query, and the changes should apply to table2 without duplication. (The query should have add, update & delete. ID is the primary key)

Please help me to create this single query. It cannot be a stored procedure. I am using SQL Server.

I tried like this but I didn't complete with an update and delete.

string query = "INSERT INTO table2 (Id, name, email) SELECT Id, name, email FROM table1";

CodePudding user response：

I would personally suggest using 3 statements in a single batch to UPDATE, INSERT and finally DELETE the appropriate rows. You could likely do this with a MERGE, but it has known "quirks" and so many do not suggest its use (See So, you want to use MERGE, eh?).

Using the 3 appropriate statements in a single batch would look something like this:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;

UPDATE T2
SET name = T1.name,
    email = T1.email
FROM dbo.Table2 T2
     JOIN dbo.Table1 T1 ON T2.ID = T1.ID;

INSERT INTO dbo.Table2 (ID, Name, Email) --I assume isn't ID is a generated value
SELECT T1.ID,
       T1.Name,
       T1.Email
FROM dbo.Table1 T1
WHERE NOT EXISTS (SELECT 1
                  FROM dbo.Table2 T2
                  WHERE T2.ID = T1.ID);

DELETE T2
FROM dbo.Table2 T2
WHERE NOT EXISTS (SELECT 1
                  FROM dbo.Table1 T1
                  WHERE T1.ID = T2.ID);

CodePudding user response：

use merge statement that can update, insert and delete in one query with following syntax

MERGE targetTable
Using sourceTable
ON mergeCondition
WHEN MATCHED
THEN updateStatement
WHEN NOT MATCHED BY TARGET
THEN insertStatement
WHEN NOT MATCHED BY SOURCE
THEN deleteStatement

in your tables it has following form

MERGE table2 t2
Using table1  t1
ON t1.Id=t2.Id
-- For Updates
WHEN MATCHED
THEN UPDATE SET 
t1.name = t2.name,t1.email  = t2.email 
-- For Inserts
when not matched BY Target then 
insert(Id,name,email) 
values (t1.Id, t1.name, t1.email) 
-- For delete
WHEN NOT MATCHED BY source 
AND t2.Id IN ( SELECT Id FROM t1)
THEN  delete;