Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
 Posted - 2002-03-04 : 18:58:26
|
| If I am doing an insert query like this:insert into blah (col1, col2, col3)select c1, c2, c3 from blah2The query will fail and rollback if there is a key violation. Is there a way to make SQL 2k skip over the records that are duplicates and continue inserting the data that is not a duplicate?Any help is greatly thanked...DanielSQL Server DBA |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-03-04 : 19:19:05
|
There is no way to ignore the PK violation.Perhaps a better solution is to avoid a PK violation in the query itself.eg. Assumes Col1 is PK in "Blah" and C1 is the new PK in "Blah2"insert into blah (col1, col2, col3)select c1, c2, c3 from blah2 B2where not exists (Select 1 from blah where Col1 = B2.C1) HTHDavidMTomorrow is the same day as Today was the day before. |
 |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2002-03-04 : 22:06:46
|
quote:
There is no way to ignore the PK violation.Perhaps a better solution is to avoid a PK violation in the query itself.eg. Assumes Col1 is PK in "Blah" and C1 is the new PK in "Blah2"insert into blah (col1, col2, col3)select c1, c2, c3 from blah2 B2where not exists (Select 1 from blah where Col1 = B2.C1) HTHDavidMTomorrow is the same day as Today was the day before.
Thats really just quite silly... So SQL cannot do something that Access does? Not even with DTS?DanielSQL Server DBA |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-05 : 14:48:00
|
| I think it's equally silly, if not more so, to expect SQL Server to guess "Oh, there's dupes in this file he wants to import, I'm gonna ASSUME he doesn't want the ENTIRE file imported, and only import part of it. I mean, if he didn't care about dupes, he wouldn't have put a primary key on the table, right?"Let me ask you a question: what if you wanted to import the file, but have the machine notify you that there are dupes (because you're not expecting any)? What if you wanted it to INSERT the new rows, and UPDATE the existing rows based on the file? Unless you plan for it and set it up, how is DTS/SQL Server supposed to know what you want? Suppose the file contains garbage data that is invalid in your database, but it imports it anyway? The hallmark of a relational database system is that it NEVER compromises data integrity. If it can't import the whole file, then it won't import any of it.I'm pretty sure there's a setting in DTS that will ignore dupe rows, but if there isn't, or you can't use it for some reason, import the data into a staging table, remove the dupes, then INSERT the remaining rows into the destination table. |
|
|
|
yakoo
Constraint Violating Yak Guru
312 Posts |
Posted - 2002-03-05 : 15:18:13
|
I agree with rob on this one.Your better off dumping your values into a staging table, removing the dublicates that exist from the first table, and then inserting your values. You could actually use blah2 as your staging table.DELETE d FROM stg_blah d INNER JOIN blah b ON (d.col1 = b.c1 AND d.col2 = b.c2 AND d.col3 = b.c3)INSERT INTO blah (col1, col2, col3) SELECT c1, c2, c3 FROM stg_blahDELETE FROM stg_blah you could even try thisINSERT INTO blah (col1, col2, col3) SELECT b2.c1, b2.c2, b2.c3 FROM blah2 b2 RIGHT JOIN blah b ON (b2.c1 = b.col1 AND b2.c2 = b.col2 AND b2.c3 = b.col13) WHERE b.c1 IS NULL [/code] |
|
|
|
|
|
|
|
|
PK violations because of duplicates