Best way to check for the existence of a record
-
Hi,
I am inserting a large number of records in one of my tables. I would like to check for the existence of that record prior to an insert. If the record already exists, I don't want to insert the same record twice. What would be the best solution to my problem? Thank you in advance.
-
There are several different ways of doing this. I would look at trying to create a view that would do something like:
CREATE VIEW vwInsertRecords
AS
SELECT t1.*
FROM tmpTable t1
LEFT JOIN DestTable t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL
Then all you would have to do is INSERT into the DestTable FROM the VIEW.
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Say for example you are going to insert record to Table1 from Table2
Insert into Table1 (PrimaryColumn,Col2,Col3)
select * from Table2 as source2
where not Exists (select *
from Table1
where PrimaryColumn = sourc2.PrimaryColumn)
Thanks,
Ganesh
-
'Cheating' way: Define a unique index that includes all columns. Insert your records one by one. When you get a 'unique index constraint violated' error, ignore it and go on to the next record
-
would be most efficient to change this query to:
where not exists (Select 1 from table1 where JOIN STATEMENT)
-
Thank you very much for your replies. Your help is greatly appreciated.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply