Inserting a record into a table

Question

Inserting a record into a table

SQLTestUser

Hall of Fame

Points: 3997
More actions
September 11, 2013 at 12:37 pm

#298645

I have a table as follows
ID Type XID Note
21 Note 1 This is a note
21 Note 2 This is not for 2
the primary key is a composite key made up of "ID+Type+XID" and i am trying to insert a new record as (22,Note,1, this is note for 22).
however i receive a message a primary key violation for inserting "1" again i think, how can i manage my insert so that the new record is inserted for 22 while ignoring the violation. It could probably be an error on design and i have no choice but to work around it.
thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

SQLTestUser (9/11/2013)
I have a table as follows
ID Type XID Note
21 Note 1 This is a note
21 Note 2 This is not for 2
the primary key is a composite key made up of "ID+Type+XID" and i am trying to insert a new record as (22,Note,1, this is note for 22).
however i receive a message a primary key violation for inserting "1" again i think, how can i manage my insert so that the new record is inserted for 22 while ignoring the violation. It could probably be an error on design and i have no choice but to work around it.
thanks.

It sounds like you are trying to insert a row with a primary key that already exists. You can't insert that row. You can't ignore a primary key violation, if you could it would destroy the whole purpose of a primary key.

Does this query return a row?

select *

from SomeTable

where ID = 22

and [Type] = 'Note'

and XID = 1

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 2

Thanks and sorry for the question, i was trying to insert the same record twice. Sorry about misleading you guys.

thanks