Need to see if a record exists before I insert into the table

Question

Post reply

Need to see if a record exists before I insert into the table

bryan.duchesne

Ten Centuries

Points: 1134
More actions
July 5, 2010 at 2:11 pm

#222893

I need to be able to run an insert but with a precheck to see if the key of my new inserted record already exists in the target table. I have tried something like
INSERT target (field1, field2, field3,.....)
SELECT field1, field2, field3
FROM Source

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Something like this?

INSERT target (field1, field2, field3,.....)

SELECT field1, field2, field3

FROM Source

WHERE NOT EXISTS (SELECT 1 FROM target t2 WHERE t2.field1 = Source.field1)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

bryan.duchesne Ten Centuries Points: 1134 More actions · Answer 2

boy... that was fast... I didnt have a chance to edit my post before your replied but sadly that didn't seem to do the trick. I still get the dups in the table.

BTW, I may also have dups in my source table as well.

LutzM SSC Guru Points: 107049 More actions · Answer 3

bryan.duchesne (7/5/2010)
boy... that was fast... I didnt have a chance to edit my post before your replied but sadly that didn't seem to do the trick. I still get the dups in the table.
BTW, I may also have dups in my source table as well.

If you have dups in your source table, which one of the rows you want to insert?

The reason for still getting dups is that you're adding rows with a non-existent value in filed1 multiple times within this single INSERT statement (=dups in your source).

Here's short example:

DECLARE @tbl_target TABLE

(

field1 INT

)

DECLARE @tbl_source TABLE

(

field1 INT

)

INSERT INTO @tbl_source

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4

INSERT INTO @tbl_target (field1)

SELECT field1

FROM @tbl_source s

WHERE NOT EXISTS (SELECT 1 FROM @tbl_target t2 WHERE t2.field1 = s.field1)

SELECT *

FROM @tbl_target

INSERT INTO @tbl_source

SELECT 1 UNION ALL

SELECT 5 UNION ALL

SELECT 5

INSERT INTO @tbl_target (field1)

SELECT field1

FROM @tbl_source s

WHERE NOT EXISTS (SELECT 1 FROM @tbl_target t2 WHERE t2.field1 = s.field1)

SELECT *

FROM @tbl_target

As you can see, the second INSERT statement will not add another field1 for value=1 but it'll add two rows with filed1=5.

You need to decide which rows to insert.

Feel free to use my short code snippet and expand it to have more columns in order to be able to show us how decide which one to select.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

bryan.duchesne Ten Centuries Points: 1134 More actions · Answer 4

Thanks for the code... I think I may have to rethink my process for doing what I need to do. Obviously, the IF EXISTS and WHERE IN won't cut it. It a question of timing and I think I need to add an addtional step before I do my inserts.

Thanks for the help.

David.Poole SSC Guru Points: 76144 More actions · Answer 5

If you've got dupes in your source then you need to include dupe handling routines in your SELECT query.

If you have a table which has ID values 1,2,3,4 in it and your source has 5,5,6,7,8 then the INSERT will fail because of the two ID=5 values. Both satisfy the WHERE NOT EXISTS at the time of insert.

The problem you may come across is where you have two records with ID=5 but no clear means of choosing the correct record. Simply aggregating the record might not work if you have two or more fields giving conflicting results.

LinkedIn Profile

Need to see if a record exists before I insert into the table

Cookies on SQLServerCentral