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 |
|
jrockfl
Posting Yak Master
223 Posts |
 Posted - 2006-07-13 : 10:37:16
|
| I have a table that needs data inserted into it. The table has a clustered index made up of 4 columns.I dont have data for one of the columns, so i just tried to insert the 'a'. But I get this message..Cannot insert duplicate key row in object 'cmprccst_sql' with unique index 'Icmprccst_sql0'.This is the SQL I'm using.INSERT INTO cmprccst_sql (item_no, item_filler, loc, curr_cd, price) SELECT item_no, 'a', loc, curr_cd, price FROM csvsingprice_rev6Any ideas? |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-07-13 : 10:44:56
|
You need to drop existing index and create one with IGNORE_DUP_KEY option as below:create unique clustered index IndexNameon tablename(col1,col2,..)with ignore_dup_key But be sure that you want duplicate values in the table !Harsh AthalyeIndia."Nothing is Impossible" |
 |
|
|
jrockfl
Posting Yak Master
223 Posts |
Posted - 2006-07-13 : 10:48:44
|
| All 4 columns do not have to have unique values right?Wouldn't just 1 have to be unique? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-07-13 : 10:53:50
|
why do you have a clustered index on 4 columns anyways?that's not very good performance wise, unless you always use all of the 4 columns in your joins, where clauses, etc...Go with the flow & have fun! Else fight the flow  blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-13 : 11:00:37
|
| The error is not due to a clustered index - it's due to a unique index.If your index Icmprccst_sql0 is the one you are talking about then the combination of the 4 columns must be unique in the table (that includes nulls oddly enough).There is already a row with those 4 column values in the table so you get an error.Either change one of the values to make them unique or update the existing row to the data you require.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jrockfl
Posting Yak Master
223 Posts |
Posted - 2006-07-13 : 11:05:12
|
| spirit1,I didn't design the table it's a "system" created table for the accounting application.nr,It's the combination of all 4 columns correct? So 3 columns could be identical, and the 4th could be unique and that would satisfy the index? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-13 : 11:06:51
|
| Yep.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jrockfl
Posting Yak Master
223 Posts |
Posted - 2006-07-13 : 11:10:04
|
| Perfect, that's what I thought.There must be a duplicate item number..grrThank you! |
|
|
|
|
|
|
|
|
Clustered Index