strnage problem with insert into

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)

strnage problem with insert into

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-06-20 : 06:00:01

i am trying to do


insert into table1(idnumber)
select idnumber from tableZZZ 
left outer join table1
on tableZZZ.idnumber=table1.idnumber
where table1.idnumber is null

which meand to add to a table idnumber which it dosnet have
bu i am reciving all the time PRIMARIY KEY VIOLATION where i insert new keys that dosent exist-where can be the problem?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-20 : 06:12:59

I'm not sure this is relevant, but does that sql not give you an "Ambiguous column name 'idnumber'." error?

insert into table1(idnumber)
select tableZZZ.idnumber from tableZZZ 
left outer join table1
on tableZZZ.idnumber=table1.idnumber
where table1.idnumber is null

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-06-20 : 06:15:41

i did tableZZZ.idnumber just forgot to type it here
and not i get a message that "cannot insert duplicate Key in object table1"

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-06-20 : 06:37:37

foudn the problem in th e source table the idnumber was twice

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-20 : 06:53:57

quote:
Originally posted by pelegk2

foudn the problem in th e source table the idnumber was twice

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

In that case use Distinct

Madhivanan

Failing to plan is Planning to fail

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-06-20 : 08:06:52

nope what i did is that i wentto the source table and made a query to check all idnumberthat appers morethen once

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources