March 30, 2006 at 1:31 am
Hello,
I want to insert the result of a select into a table with a primary key. To avoid primary key constraint violation I have to filter out the records that already exist in the target table.
This seems to work
INSERT INTO Target
SELECT DISTINCT x, y, a
FROM Source
WHERE NOT EXISTS (SELECT x FROM Target WHERE Source.x = Target.x AND Source.y = Target.y)
Is there something more generic ? like INSERT UNIQUE ? INSERT NEW ?
Patrick Duflot
March 30, 2006 at 1:41 am
No, nothing like INSERT UNIQUE - your query is fine.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 31, 2006 at 5:58 am
Assuming that X and Y are the PK, I would use the following variation on the common "not-in" query
insert into dest(x,y,a)
select source.x,source.y,source.a
from source
left outer join dest on (source.x=dest.x) and (source.y=dest.y)
where (dest.x is null) and (dest.y is null)
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
March 31, 2006 at 8:10 am
If you used a unique index rather than a primary key or unique constraint, you could use the IGNORE_DUP_KEY option. Insert all the rows you like, and the duplicates will be ignored. You will get a warning instead of an error message.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply