SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Inserting multiple records - duplicate problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Fromper
Starting Member

USA
30 Posts

Posted - 01/16/2013 :  16:10:43  Show Profile  Reply with Quote
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

4612 Posts

Posted - 01/16/2013 :  17:09:03  Show Profile  Reply with Quote
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
Go to Top of Page

Fromper
Starting Member

USA
30 Posts

Posted - 01/16/2013 :  17:22:49  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 01/16/2013 :  17:43:04  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 01/16/2013 :  17:50:49  Show Profile  Reply with Quote
You could also create you priamry with using the IGNORE_DUP_KEY = ON option. But, I would recomend against doing that.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/16/2013 :  21:49:58  Show Profile  Reply with Quote
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 )
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000