Best way to see if Record exists

Last Post 12 Dec 2006 01:19 PM by PDinCA. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
tcarnes
New Member
New Member

--
14 Sep 2006 12:17 PM
I will be periodically running scripts to add data to SQL tables, and I would like to check to see if the record already exists before I do the Insert. I was thinking of doing a SELECT Count query:

set @record_count = Select Count(*) from Table where col1 = 43 and col2 = 'Mary' and col3 = 'Smith'

if @record_count = 0
{
// Do Insert Here
}


but don't know if this is the best way? Any better ideas?

Terry
nosepicker
New Member
New Member

--
14 Sep 2006 01:58 PM
This isn't necessarily faster, but you can also do this:

IF NOT EXISTS (SELECT 1 FROM Table WHERE col1 = 43 AND col2 = 'Mary' AND col3 = 'Smith')
BEGIN
...
END
tcarnes
New Member
New Member

--
14 Sep 2006 03:15 PM
Thanks! I like yours better. Seems cleaner and a tad more elegant.
ac_oneworld
New Member
New Member

--
04 Oct 2006 05:26 AM
Only problem is there is a chance in the previous examples that a row may be committed inbetween your select and insert. You need to lock to ensure uniqueness, an implicit lock (single statement) would probably be safest, i.e.


insert into region
select 1 as regionid, 'Eastern' as regiondescription
where not exists (select * from region where regiondescription = 'Eastern')
myLittleTools
New Member
New Member

--
18 Oct 2006 09:06 PM
Another option would be to consider using the new "upsert" functionnality (only available in SQL 2005)
Considering you have all the datas to be inserted located in a temp table tmpTable then

MERGE INTO myTable
USING tmpTable
ON myTable.col1 = tmpTable.col1
WHEN MATCHED THEN
UPDATE...
WHEN NOT MATCHED THEN
INSERT ....

Hope this helps

_____________________________________
myLittleAdmin for SQL Server 2005
The First Web-Based Management Tool
Specially Designed For SQL Server 2005
PDinCA
New Member
New Member

--
12 Dec 2006 01:19 PM
I should mention that this is occurring in SS2000 but as this thread is in ss2005 I hope forgiveness will be forthcoming and help with this nasty problem can be given...

Does anyone have any advice on preventing BOTH records getting into the destination table WITHOUT a UNIQUE Index (because that would stop ANY records being inserted as soon as one "duplicate" is encountered...) or a DISTINCT (see later)?

CREATE TABLE #email (email NVARCHAR(120), id int)

INSERT INTO #email
SELECT *
FROM ( SELECT 'abc@def.net' as EMail,1 as ID
union
select 'abc@def.net',2
) AS i
WHERE NOT EXISTS ( SELECT 1 FROM #email e WHERE e.email = i.email )

SELECT * FROM #email

gives:
--- email --- id
abc@def.net 1
abc@def.net 2

DROP TABLE #Email

I tried all four variations of the SET TRANSACTION ISOLATION LEVEL ... no difference in the results.

It appears that the derived table is built, the WHERE NOT EXISTS is evaluated on each record from the derived table against the "starting" record set in the #Email table, NOT, as I wanted, on the records the table started with PLUS those inserted from the derived table to the point where my "duplicate" is inserted.

ANY IDEAS HOW TO GET AROUND THIS?

BTW: This is a cut-down of the actual row - there are other ID's (say ID2) and a "Distribution List". The same email may be used by more than 1 ID record and by a NULL or NOT NULL ID2 value that is pulled within the derived table "i", so a simple DISTINCT doesn't fly either.
You are not authorized to post a reply.

Acceptable Use Policy