06-15-2022 01:57 PM
I need to mimic Informatica’s simple connected lookup process. I want to look for a record on a target table and if it exists, update it, otherwise insert it. I have tried to use the Lookup Snap, but I haven’t been able to make it work for me. Does someone have an example or a pattern that I can see?
Solved! Go to Solution.
06-15-2022 02:19 PM
@Damone
do it all in SQL
ie
IF EXIST(Select id from table_x where id=@id)
BEGIN
UPDATE table_x set col1=@col1 where id=@id
END
ELSE
BEGIN
INSERT INTO into table_x (id,col1) values(@id,@col1)
END
06-15-2022 02:19 PM
@Damone
do it all in SQL
ie
IF EXIST(Select id from table_x where id=@id)
BEGIN
UPDATE table_x set col1=@col1 where id=@id
END
ELSE
BEGIN
INSERT INTO into table_x (id,col1) values(@id,@col1)
END
06-16-2022 10:26 AM
Thank you. That is definitely useful and I will go that route going forward. In this instance, one important thing that I forgot to mention is that my source is Oracle DB, and my target and lookup tables are in SQL Server. So I am selecting from Oracle, checking a SS table to see if the attribute exists, and if not, then insert / update.
06-16-2022 10:36 AM
I got it. I’ll use Snaplogic to write the data to a SS temp table, then use the SQL above on the SS side for the upsert. Thanks!
06-19-2022 07:49 AM
@jcornelius: Could we have used a router snap instead, putting 2 different conditions and inserting/updating depending on the output of the condition in router snap?