If it exists, update it; if not, create it

I've got this query:

update PFaids2005.dbo.work_study_tracking

set dept_fws=e.dept_fws,
supr_fws=e.supr_fws,
telephone_fws=e.telephone_fws,
email_fws=e.email_fws,
dept_sws=e.dept_sws,
supr_sws=e.supr_sws,
telephone_sws=e.telephone_sws,
email_sws=e.email_sws,
dept_iws=e.dept_iws,
supr_iws=e.supr_iws,
telephone_iws=e.telephone_iws,
email_iws=e.email_iws,
dept_ows=e.dept_ows,
supr_ows=e.supr_ows,
telephone_ows=e.telephone_ows,
email_ows=e.email_ows

from Manual.dbo.FWSP_Employment_Roll as e
where PFaids2005.dbo.work_study_tracking.stu_ay_token=e.stu_ay_token

If the record doesn't exist in the table being updated, I'd like to create a record with the values I'm selecting.

Any guidance?

Use a query like this:

INSERT ... WHERE NOT EXISTS (...)

I'm not sure at all what goes in the place of the ellipses in your example. :confused:

INSERT INTO yourtarget (col1, col2, col3, ... , coln)
SELECT yourvalues
FROM yoursource
WHERE NOT EXISTS (
SELECT 1 FROM yourtarget
WHERE col1 = yourval1 AND col2 = yourval2 AND ...
)

1 Like

I'm confused on the two different SELECT / WHERE statements.

The first one selects the values to be inserted. The second one ensures that no duplicate rows are inserted

So, this?:

INSERT INTO pfaids2005.dbo.work_study_tracking (stu_ay_token,
dept_fws,
supr_fws,
telephone_fws,
email_fws,
dept_sws,
supr_sws,
telephone_sws,
email_sws,
dept_iws,
supr_iws,
telephone_iws,
email_iws,
dept_ows,
supr_ows,
telephone_ows,
email_ows) 

SELECT stu_ay_token,
dept_fws,
supr_fws,
telephone_fws,
email_fws,
dept_sws,
supr_sws,
telephone_sws,
email_sws,
dept_iws,
supr_iws,
telephone_iws,
email_iws,
dept_ows,
supr_ows,
telephone_ows,
email_ows

FROM Manual.dbo.FWSP_Employment_Roll as f

WHERE NOT EXISTS (
SELECT 1 FROM PFaids2005.dbo.work_study_tracking as w
WHERE w.stu_ay_token = f.stu_ay_token
)

looks like it should work.

btw lookup the OUTPUT clause. You can see what rows would be inserted. Also, hope your testing in development, not productioN!

I'll check out the OUTPUT clause. (And yes, I am using a test DB. :smile:)

If you want to do this in one shot (i.e. do both the update and insert) and you're using SQL 2008 or higher, you may want to consider the MERGE statement.

The old Sybase UPDATE.. FROM.. does not work. I do not mean it is slow or proprietary; I mean it gives the wrong results. Google it. When you have a 1:m update, it grabs the last physical record in physical storage and uses it. It should give an error.

Please learn all the ways and rows and records are different. Look at the MERGE statement for this job.