| Author |
 Topic  |
|
|
Fromper
Starting Member
USA
30 Posts |
 Posted - 01/16/2013 : 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
Flowing Fount of Yak Knowledge
3824 Posts |
Posted - 01/16/2013 : 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. |
Edited by - Lamprey on 01/16/2013 17:13:45 |
 |
|
|
Fromper
Starting Member
USA
30 Posts |
Posted - 01/16/2013 : 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
Flowing Fount of Yak Knowledge
3824 Posts |
|
|
Lamprey
Flowing Fount of Yak Knowledge
3824 Posts |
Posted - 01/16/2013 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 01/16/2013 : 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 ) |
|
|
| |
Topic |
|
Inserting multiple records - duplicate problem
