CODEWITHSUNDEEP
sqlsql-servert-sql
user10537311
user10537311

Reputation:

Merge 2 table with condition

I've got a task to insert all table into next one info with condition. I need to merge the same value like Abonentas table primary key (AbonentoID), And where miestoID is 1. I've tried function merge but I've gotten an error after the Inner Join. I've searched a lot of info about it and I can't find the solution.

Error

Incorrect syntax near the keyword 'WHEN'

Code

MERGE dbo.VipAbonentai vip 
USING (SELECT ab.abonentoID From Abonentas as ab
Inner join Asmuo as A on ab.asmensID = A.asmensID
Inner join Miestas as M on M.miestoID = M.miestoID
Where M.miestoID = 1)
WHEN MATCHED
THEN UPDATE SET 
    vip.abonentoID = ab.abonentoID,
WHEN NOT MATCHED BY TARGET  
THEN
INSERT (AbonentoID, asmensID, planoID, numeris, sutartiesPradzia, 
sutartiesPabaiga)
VALUES (ab.AbonentoID, ab.asmensID, ab.planoID, ab.numeris, 
ab.sutartiesPradzia, ab.SutartiesPabaiga) ;

Upvotes: 1

Views: 57

Answers (1)

aduguid
aduguid

Reputation: 3195

Here's how I'd write the merge.

The following columns need to be returned in the VipAbonentai_source. I'm not sure about which tables they are coming from. I've written the statement as a common table expression (CTE).

  ab.[abonentoID]
, [asmensID]
, [planoID]
, [numeris]
, [sutartiesPradzia]
, [sutartiesPabaiga]

If you wrap it in a transaction, you can run it on a rollback without effecting any records. Also, I output the records to see what will change. If you have triggers on the table, you'll have to comment out the OUTPUT line.

SET XACT_ABORT ON;
BEGIN TRANSACTION;

WITH 
VipAbonentai_source
AS 
(
    SELECT 
         ab.[abonentoID]
       , [asmensID]
       , [planoID]
       , [numeris]
       , [sutartiesPradzia]
       , [sutartiesPabaiga]
    FROM 
      Abonentas AS ab
      INNER JOIN Asmuo AS A ON ab.[asmensID] = A.[asmensID]
      INNER JOIN Miestas AS M ON M.[miestoID] = M.[miestoID]
    WHERE 
      M.[miestoID] = 1
) 
MERGE dbo.VipAbonentai AS T
USING VipAbonentai_source AS S
ON T.[abonentoID] = S.[abonentoID]
WHEN NOT MATCHED BY TARGET
THEN INSERT
(
  [abonentoID], [asmensID], [planoID], [numeris], [sutartiesPradzia], [sutartiesPabaiga]
)
VALUES
(
  S.[abonentoID], S.[asmensID], S.[planoID], S.[numeris], S.[sutartiesPradzia], S.[sutartiesPabaiga]
)
WHEN MATCHED
THEN UPDATE SET
 T.[asmensID] = S.[asmensID]
, T.[planoID] = S.[planoID]
, T.[numeris] = S.[numeris]
, T.[sutartiesPradzia] = S.[sutartiesPradzia]
, T.[sutartiesPabaiga] = S.[sutartiesPabaiga]
--WHEN NOT MATCHED BY SOURCE --< You may want to add a WHERE clause here
--THEN DELETE
OUTPUT @@SERVERNAME AS [Server Name], DB_NAME() AS [Database Name], $action, inserted.*, deleted.*;

ROLLBACK TRANSACTION;
--COMMIT TRANSACTION;

GO

Upvotes: 1

Related Questions