Search code examples
sqlsql-serversnaplogic

How to insert into a SQL table via snaplogic pipeline depending on the existence of a data

I am new to snaplogic. I have 2 tables(A, B) in SQL Server as below. I need to insert data from table A into table B based on the existence of numberID in B.

  • If numberID of A exists in B, I need to update the name in B.
  • If numberID of A doesn't exist in B, I have to insert the row in table B.

I would like to know how to perform case disjunction. I wanted to use a router or a conditional snap but I don't understand how to use them with the result of the SQL query. I need help please.

Solution

  • you can use merge function

    MERGE INTO B AS Target  
USING (select * from A)  
       AS Source 
ON Target.id = Source.id  
WHEN MATCHED THEN  
UPDATE SET B.Name = Source.Name
WHEN NOT MATCHED BY TARGET THEN  
INSERT (Name) VALUES (source.NewName)

    https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15