Solved

DTS Primary Key Violation error bypass

Posted on 2011-09-20
11
289 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
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 
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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Suggested Solutions

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

696 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