Solved

DTS Primary Key Violation error bypass

Posted on 2011-09-20
11
288 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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
 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.

831 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