primary key error - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

primary key error

Author

Topic

Rg
Starting Member

10 Posts

Posted - 2011-12-22 : 03:34:25

I have 3000000 Insert script and i forgot to add if not exists (select id,name from transaction where id=1 and name='ABC') begin insert into transactions(id,name) values (1,'ABC') end

i am facing primary key error , can you please help me ASAP

below is query , how i add if not exists or any other solution for that
insert into Transactions (id,name) values (1,'ABC')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-22 : 04:12:11

it may be that your values itself might have duplicate sets among them. Are you passing harcoded values as shown above or are you getting values from another table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Rg
Starting Member

10 Posts

Posted - 2011-12-22 : 04:13:54

passing harcoded values below is example
insert into Transactions (id,name) values (1,'ABC')
insert into Transactions (id,name) values (2,'aa')
insert into Transactions (id,name) values (3,'vv')
insert into Transactions (id,name) values (4,'vv')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-22 : 04:18:15

what are columns involved in pk?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Rg
Starting Member

10 Posts

Posted - 2011-12-22 : 04:23:43

primary key is on ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-22 : 05:46:21

if data already exists,what does this return?


SELECT ID
FROM Transactions 
GROUP BY ID
HAVING COUNT(*) > 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Rg
Starting Member

10 Posts

Posted - 2011-12-22 : 06:05:14

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Hotels'. Cannot insert duplicate key in object 'dbo.transactions'. The duplicate key value is (442, ).
The statement has been terminated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-22 : 06:11:04

quote:
Originally posted by Rg

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Hotels'. Cannot insert duplicate key in object 'dbo.transactions'. The duplicate key value is (442, ).
The statement has been terminated.

check for duplicates using the provided suggestion first

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Rg
Starting Member

10 Posts

Posted - 2011-12-22 : 08:55:13

actually i have already created [3000000] insert script just how to add if not exists in the script

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-22 : 11:06:09

if you're inserting harcoded values you cant use not exists check.
for hardcoded values best way is to put them in excel and then check for duplicates
for insert...select syntax you can add not exists condition like below


insert into desttable
select columns...
from sourcetable s
where not exists (select 1 from desttable where pk= s.pk)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-12-22 : 11:16:30

Or you can insert the file into a scratch work table without the constraints that matches the schema of the final table in other regards.

Then interrogate the data there.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-22 : 11:57:30

where are you getting the 3 million rows of data from?

And the staging table is the real way to go

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-22 : 12:08:51

quote:
Originally posted by visakh16

if you're inserting harcoded values you cant use not exists check.
for hardcoded values best way is to put them in excel and then check for duplicates
for insert...select syntax you can add not exists condition like below

insert into desttable
select columns...
from sourcetable s
where not exists (select 1 from desttable where pk= s.pk)

I Wouldn't say you can't. But, probalby not the best way:


INSERT FOO 
(pk)
SELECT (1) 
WHERE NOT EXISTS (SELECT * FROM Foo WHERE pk = 1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources