Inserting multiple records - duplicate problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Inserting multiple records - duplicate problem

Author

Topic

Fromper
Starting Member

33 Posts

Posted - 2013-01-16 : 16:10:43

Does SQL insert many records into a table one at a time, or all at once?

The reason I ask is that we're having trouble with a duplicate primary key error. We're doing an insert from a select statement from other tables, and our query intentionally looks for duplicates and excludes them.

If the record is already in the target table before the insert, then the insert will skip it and not insert that record.

But the problem seems to happen when the insert is trying to put in two records with the same primary key. I think our query is built around the assumption that the first will go in correctly, then the second will be skipped because it no longer meets the requirement of not being a dup. But it doesn't seem to be working that way. It seems to be trying to insert both of them, and crashing on the second one, which of course rolls back the entire insert.

The format of our query is:

INSERT INTO TargetTable
SELECT various field names
FROM Source1
JOIN Source2
ON Source1.whatever = Source2.whatever
LEFT JOIN TargetTable
ON new record's primary key = TargetTable.primarykey
WHERE TargetTable.primarykey IS NULL

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-01-16 : 17:09:03

The short answer is that it happens all at once. Basically, if you executed the SELECT portion of your query (minus the insert), that is what SQL will try and insert.

Fromper
Starting Member

33 Posts

Posted - 2013-01-16 : 17:22:49

Yeah, that's what I suspected, which explains our issue.

I'm currently testing a solution that will only insert the first record of several that would otherwise create a duplicate primary key. Here's the basic format of my solution:

-- This part is the same as in the first post

INSERT INTO TargetTable
SELECT various field names
FROM Source1
JOIN Source2
ON Source1.whatever = Source2.whatever

--This next part is what's new, and should grab just the first record of several

JOIN (same select as four lines above, but also selects
ROW_NUMBER() over(partition by fields that make TargetTable's primary key
order by remaining fields) as RowNum
) as NoDups
on Every field being selected = same field in NoDups
and 1 = NoDups.RowNum

-- This last part is the same as what's in the first post

LEFT JOIN TargetTable
ON new record's primary key = TargetTable.primarykey
WHERE TargetTable.primarykey IS NULL

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-01-16 : 17:43:04

I think you are making it far more complicated than it needs to be. If you want to post some DDL, DML and expected results we can show you how to do it in a much more simple way. Here are some links that can help with that:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-01-16 : 17:50:49

You could also create you priamry with using the IGNORE_DUP_KEY = ON option. But, I would recomend against doing that.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2013-01-16 : 21:49:58

quote:
Originally posted by Fromper

Yeah, that's what I suspected, which explains our issue.

I'm currently testing a solution that will only insert the first record of several that would otherwise create a duplicate primary key. Here's the basic format of my solution:

-- This part is the same as in the first post

INSERT INTO TargetTable
SELECT various field names
FROM Source1
JOIN Source2
ON Source1.whatever = Source2.whatever

--This next part is what's new, and should grab just the first record of several

JOIN (same select as four lines above, but also selects
ROW_NUMBER() over(partition by fields that make TargetTable's primary key
order by remaining fields) as RowNum
) as NoDups
on Every field being selected = same field in NoDups
and 1 = NoDups.RowNum

-- This last part is the same as what's in the first post

LEFT JOIN TargetTable
ON new record's primary key = TargetTable.primarykey
WHERE TargetTable.primarykey IS NULL

Does multiple column make primary key? Yes you can use ROW_NUMBER() OVER (PARTITION by....) to filter out whatever you need and insert into Target table. You can check with NOT EXISTS( Select * from Target Table Where Field1 = PreviousQuery.Field1.....Field2..Field3 and so on )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources