This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Accept
Reject
Preferences
FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

How to Create a SQL Server Connected Lookup

Go to solution
Damone
New Contributor

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

0 Kudos
1 ACCEPTED SOLUTION

Go to solution
jcornelius
New Contributor III

‎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

View solution in original post

5 REPLIES 5

Go to solution
jcornelius
New Contributor III
In response to darshthakkar

‎06-20-2022 05:19 AM

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.