Solved

DTS Primary Key Violation error bypass

Posted on 2011-09-20
11
284 Views
Last Modified: 2012-05-12
sql server 2008 r2 standard

I have a text file one field "Material" has duplicates in the field. (source import file)

In my Table..."Material"  is a PK field. (Destination Table)

Using DTS...is there a way to keep importing the data even if a key violation occurs..and just does not import the
violation record ?


Thanks
fordraiders
0
Comment
Question by:fordraiders
  • 6
  • 5
11 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 36567588
Consider importing into a temp table first ... then you can insert into the real table from the temp table and "join out" the duplicates.
0
 
LVL 3

Author Comment

by:fordraiders
ID: 36568158
well I tried this and it deleted  both my records ?
DELETE FROM crs_staging
WHERE material_no  IN
    (SELECT material_no
    FROM crs_staging
    GROUP BY  material_no
    HAVING COUNT(*) > 1)
0
 
LVL 3

Author Comment

by:fordraiders
ID: 36568161
did not retain 1 record from the dups...
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 36568191
I see, I thought the issue was an attempt to insert a record from crs_staging that already existed in real table -- I didn't realize the dupes would both be in the temp table crs_staging.  Well, there are several ways to handle this.  Are the rows duplicated all the way across every column -- or just on the material_no field?

0
 
LVL 3

Author Comment

by:fordraiders
ID: 36568205
every field...but I'm just concerned about "material_no"
if theres a dup delete it
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 3

Author Comment

by:fordraiders
ID: 36568211
crs_staging is the real table
0
 
LVL 3

Author Comment

by:fordraiders
ID: 36568215
just trying to do it all in one sql
0
 
LVL 33

Accepted Solution

by:
knightEknight earned 500 total points
ID: 36568246
Here is one way to do it in principle, meaning it may or may not be wise depending on other factors, but here it is ...

First, BACK UP the data ... then:

select distinct *
into crs_staging_TEMP
from crs_staging
GO

truncate table crs_staging
GO

insert into crs_staging
select * from crs_staging_TEMP

0
 
LVL 33

Expert Comment

by:knightEknight
ID: 36568256
then, once you confirm the data is good, truncate and drop the TEMP table.

Again, this solution assumes that all columns are duplicated, not just the material_no column.
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 36568289
Depending on the size of the table and the number of duplicate rows, this variant of the same solution may be more efficient:


select distinct *
into crs_staging_TEMP
from crs_staging
WHERE material_no  IN
    (SELECT material_no
    FROM crs_staging
    GROUP BY  material_no
    HAVING COUNT(*) > 1)
GO

delete from crs_staging
WHERE material_no  IN
    (SELECT material_no
    FROM crs_staging
    GROUP BY  material_no
    HAVING COUNT(*) > 1)
GO

insert into crs_staging
select * from crs_staging_TEMP

-- again, same assumptions apply
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 36568355
Thanks...this will do the job in one swoop also...
with CTE as (
      select *, row_number() over (partition by material_no order by material_no) rn
      from crs_staging
)
delete from CTe where rn > 1
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now