Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

DTS Primary Key Violation error bypass

Posted on 2011-09-20
11
Medium Priority
?
298 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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
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…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

580 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