November 24, 2008 at 10:40 am
how would i get around this issue with 2 columns
that i need to insert with the same values.
insert into tablename (col1, col2)
select account, account
Violation of PRIMARY KEY constraint . Cannot insert duplicate key in object
Do i need a where clause or cursor??
November 24, 2008 at 10:45 am
The problem isn't that it's the same value in both columns, it's that you're trying to add the same row more than once.
Either add a Where clause that makes it not insert rows that already exist, or an Except clause (look up Intersect/Except in Books Online) for the same thing. Also make sure your insert doesn't include duplicate rows, possibly with the Distinct statement after Select. (The second thing only applies if you are inserting more than one row at a time.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 24, 2008 at 10:51 am
begs the question, if you find yourself having to store the same data in a column should it really be a primary key??
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 24, 2008 at 11:25 am
Absolutely not a cursor.
If you're getting the error that you're violating the primary key, it's simply because you are, in fact, violating the primary key.
The purpose of the primary key is to ensure a lack of duplicate rows by defining a unique identifier, either in the form of a single column, or with multiple columns defining the unique value. But whatever defines that unique value, it MUST be unique to be inserted. No choice.
So, either your data is off, or you have the wrong columns identified as the key on the table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 24, 2008 at 12:26 pm
when i check the current table col1 and col2 are the same? col1 and col2 are both primary keys, so how is this possible and how can i insert into these 2 columns
November 24, 2008 at 12:36 pm
First both columns can't be primary keys unless they form a multi-column primary key.
Read the article below in my signature block about how to ak for help. Without the information detailed in the article, we can't help you.
November 24, 2008 at 12:55 pm
It's not the each one is a primary key. You're looking at the GUI and seeing a key next to each column. That means they're part of a compound primary key. Compound meaning it has more than one column. It's no big deal. It's a normal circumstance in fact.
At this point, you're going to have post code & structures to get detailed help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 24, 2008 at 1:17 pm
when you run an insert statement, it can be ran only once then you get an error?
November 24, 2008 at 1:23 pm
You can only run it once for a particular set of values, yeah.
Here, an example:
CREATE TABLE dbo.x
(ColNo1 INT NOT NULL,
ColNo2 INT NOT NULL,
Val VARCHAR(50) NULL)
ALTER TABLE dbo.x ADD CONSTRAINT [PK_MyPK] PRIMARY KEY CLUSTERED
(
ColNo1 ASC,
ColNo2 ASC
)
--this works
INSERT INTO dbo.x (
ColNo1,
ColNo2,
Val
) VALUES (
0,
0,
'Value zero' )
--this works
INSERT INTO dbo.x (
ColNo1,
ColNo2,
Val
) VALUES (
1,
0,
'Value 1 & zero' )
--even this works
INSERT INTO dbo.x (
ColNo1,
ColNo2,
Val
) VALUES (
1,
1,
'Value zero' )
--this will cause an error
INSERT INTO dbo.x (
ColNo1,
ColNo2,
Val
) VALUES (
0,
0,
'Value zero' )
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 24, 2008 at 1:27 pm
got it thanks
November 24, 2008 at 1:27 pm
Try this in a test database (sandbox if you will).
CREATE TABLE dbo.Table_1
(
MyColumn1 int NOT NULL,
MyColumn2 int NOT NULL,
MyData1 varchar(50) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table_1 ADD CONSTRAINT
PK_Table_1 PRIMARY KEY CLUSTERED
(
MyColumn1,
MyColumn2
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
insert into dbo.Table_1 (MyColumn1, MyColumn2)
select 1, 1; -- will work
go
insert into dbo.Table_1 (MyColumn1, MyColumn2)
select 1, 1; -- will fail
go
drop table dbo.Table_1;
go
November 24, 2008 at 1:29 pm
got it thanks
November 24, 2008 at 3:25 pm
the compound key has to be unique, you can have the same value in single columns as long as the combined values are unique
col1 col2
1 1
1 3
1 5
1 6
2 9
1 9
will all be fine. Try to insert 1, 1 and it will fail. Does this make sense
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply