Checking for the non existence of a record

Question

Checking for the non existence of a record

franklores

Grasshopper

Points: 10
More actions
October 20, 2011 at 6:57 am

#245630

Hello,
Please excuse if this is too simple, but I'm having a bit of a problem trying to figure the logic out on this one.
I have a contact table with a field contact_id and a external references table with a foreign key to contact and a field for external references, this is so we can capture the external reference used by external agencies for a given contact.
My script has to be able to insert a new reference into the external references table if it doesn't already exist.
I know how to do this with an if then else scenario but I want to be able to do it to a whole dataset, not individual rows.
I hope it's not too confusing and thanks in advance.
Frank

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Frank

Please will you post table DDL in the form of CREATE TABLE statements, sample data in the form of INSERT statements, and show us the data you're trying to insert into the table.

Thanks

John

Sean Lange SSC Guru Points: 286609 More actions · Answer 2

Something like this help you get started?

insert xref

select c.contact_id, [All other data columns here]

from contact c

left join xref x on c.contact_id = x.contact_id

where x.contact_id is null

If you want actual code you will have to post ddl and sample data as John already said.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 3

You could also use WHERE NOT EXISTS with a similar query like Sean's code.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

franklores Grasshopper Points: 10 More actions · Answer 4

Thank you all.

The code supplied pointed me in the right direction.

Thanks again.