Solved

DTS Primary Key Violation error bypass

Posted on 2011-09-20
11
286 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
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.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

776 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