Forum Discussion

Damone's avatar
Damone
New Contributor
4 years ago
Solved

How to Create a SQL Server Connected Lookup

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?

  • @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

5 Replies

  • @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

    • Damone's avatar
      Damone
      New Contributor

      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.

      • Damone's avatar
        Damone
        New Contributor

        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!

    • darshthakkar's avatar
      darshthakkar
      Valued Contributor

      @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?

      • jcornelius's avatar
        jcornelius
        New Contributor III

        It would depend on if you have the data. I was assuming all the data was in SQL, so you would not want to make 2 trips to the DB, 1 to find out if the table existed and then 1 to write the data, when it all is easily done in 1 SQL statement…now if we are using different DB’s them surely you could read DB 1 then route the commands to DB 2.