How to find which Key is Duplicate in Insert

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 2000 Forums

SQL Server Development (2000)

How to find which Key is Duplicate in Insert

Author

Topic

CLages
Posting Yak Master

116 Posts

Posted - 2006-11-15 : 07:45:02

Hi

I have two tables

MainTable

Code(key) Name
123 aaaa
124 bbbb
125 cccc

ChildTable

Code(key) Name
333 xxxx
444 yyyyy
124 zzzzz

if i do

Insert into MainTable

select code, name from ChildTable

Obviously i get an error
"Can not Insert Duplicate key etc,etc"

But MSsql dont show me what is the Duplicate key (in this case 124)

then i have to do a SQL with join to find out which one is duplicated

IS there any way i get the Duplicate key in the moment of the error?

Tks

C. Lages

Inserty

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-15 : 07:47:52

Why at the time of error?

Insert only those which don't exist in MainTable.

Insert into MainTable
 (code, Name)
Select Code, Name
From childTable c
Where Not Exists (select * from MainTable m where m.Code = c.Code)

Harsh Athalye
India.
"Nothing is Impossible"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-15 : 07:51:10

But MSsql dont show me what is the Duplicate key (in this case 124)
Nopes MS SQL Server is not smart..

Chirag

http://chirikworld.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-15 : 07:53:43

quote:
Nopes MS SQL Server is not smart.

Thanks to Microsoft!!

Otherwise we all would have been jobless, selling pencils on the street!

Harsh Athalye
India.
"Nothing is Impossible"

CLages
Posting Yak Master

116 Posts

Posted - 2006-11-15 : 11:05:31

Harsh Athalye
If i do what you suggested (I know its works)
but i will never know how many rows was not inserted

The main idea was in time of runtime, whem sql Abort, tell me in which key its gets the problems.

About "MS SQL Server is not smart.." i think its very easy to be implemented by MS, because just Display the KEy that sql was trying to
Insert.

C. Lages

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-15 : 11:45:14

The main idea was in time of runtime, whem sql Abort, tell me in which key its gets the problems.

Is your insert query is aboroted then by default whole transaction will be rollback.. unless and untill you are trying to insert in some loop. Ifs its in a same batch then it should rollback. Also if you want to find how many rows are affected then you will be able to know by using @@Rowcount.

and secondly if you want to do then before insert you can have a select clause on which all records are duplicated.

the query like this will give you all the records which are duplicate, you require to avoid them in the insert statements.


Select Code, Name
From childTable c
Where Exists (select * from MainTable m where m.Code = c.Code)

Chirag

http://chirikworld.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources