SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 primary key error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rg
Starting Member

India
10 Posts

Posted - 12/22/2011 :  03:34:25  Show Profile  Reply with Quote

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

India
52249 Posts

Posted - 12/22/2011 :  04:12:11  Show Profile  Reply with Quote
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/

Go to Top of Page

Rg
Starting Member

India
10 Posts

Posted - 12/22/2011 :  04:13:54  Show Profile  Reply with Quote
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')
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/22/2011 :  04:18:15  Show Profile  Reply with Quote
what are columns involved in pk?

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

Go to Top of Page

Rg
Starting Member

India
10 Posts

Posted - 12/22/2011 :  04:23:43  Show Profile  Reply with Quote
primary key is on ID
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/22/2011 :  05:46:21  Show Profile  Reply with Quote
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/

Go to Top of Page

Rg
Starting Member

India
10 Posts

Posted - 12/22/2011 :  06:05:14  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/22/2011 :  06:11:04  Show Profile  Reply with Quote
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/

Go to Top of Page

Rg
Starting Member

India
10 Posts

Posted - 12/22/2011 :  08:55:13  Show Profile  Reply with Quote
actually i have already created [3000000] insert script just how to add if not exists in the script
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/22/2011 :  11:06:09  Show Profile  Reply with Quote
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/

Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 12/22/2011 :  11:16:30  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 12/22/2011 :  11:57:30  Show Profile  Reply with Quote
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/


Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4361 Posts

Posted - 12/22/2011 :  12:08:51  Show Profile  Reply with Quote
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)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000