| Author |
 Topic  |
|
|
Rg
Starting Member
India
10 Posts |
 Posted - 12/22/2011 : 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
India
47173 Posts |
Posted - 12/22/2011 : 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
India
10 Posts |
Posted - 12/22/2011 : 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
India
47173 Posts |
Posted - 12/22/2011 : 04:18:15
|
what are columns involved in pk?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Rg
Starting Member
India
10 Posts |
Posted - 12/22/2011 : 04:23:43
|
| primary key is on ID |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 12/22/2011 : 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
India
10 Posts |
Posted - 12/22/2011 : 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
India
47173 Posts |
Posted - 12/22/2011 : 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
India
10 Posts |
Posted - 12/22/2011 : 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
India
47173 Posts |
Posted - 12/22/2011 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3442 Posts |
Posted - 12/22/2011 : 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 |
|
|
Lamprey
Flowing Fount of Yak Knowledge
3833 Posts |
Posted - 12/22/2011 : 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) |
|
|
| |
Topic |
|
primary key error
