Please help: What's the Error for Statement: Insert with Not Exists

[zengdefu]

create table #temp1 (col1 varchar(5) not null primary key(col1))
create table #temp2 (col1 varchar(5))
insert into #temp2 values ('123')
insert into #temp2 values ('abc')
insert into #temp2 values ('123')
insert into #temp2 values ('456')
select * from #temp1
select * from #temp2

insert into #temp1
select a.col1
from #temp2 a
where not exists (select 1 from #temp1 where a.col1 = col1)

Error as below:
Server: Msg 2627, Level 14, State 1, Line 12
Violation of PRIMARY KEY constraint 'PK__#temp1__2A16940C'. Cannot insert duplicate key in object '#temp1___________________________________________ __________________________________________________ _________________0000001B6106'.
The statement has been terminated.

The database is SQL Server 2K

------------------------------------
Thanks for the help. But I cannot use Distinct and In in my query because the real case is I have to insert more than 10 columns and 3 of them are key columns.

[MAK]

--your select statement brings duplicate data. so it cannot be inserted into a primary key column.

create table #temp1 (col1 varchar(5) not null primary key(col1))
create table #temp2 (col1 varchar(5))
insert into #temp2 values ('123')
insert into #temp2 values ('abc')
insert into #temp2 values ('123')
insert into #temp2 values ('456')
select * from #temp1
select * from #temp2

insert into #temp1(col1)
select distinct a.col1 as col1
from #temp2 a
where a.col1 not in (select col1 from #temp1)

[zengdefu]

Thanks for the help. But I have checked the data in #temp1 using NOT EXISTS. The point here is why the NOT EXISTS does not work. :P

[skhanal]

INSERT operates on the set of rows, not on individual records you are trying to insert, that is it does not insert one row and check the condition again.

So when SQL Server checks for row with '123' that row is not in temp1, since temp1 is empty all rows qualify. So when it tries to insert it fails.

[zengdefu]

Thanks, that meant this is behavour of NOT EXISTS/EXIST operation.

[skhanal]

All SQL Commands operate on set of records. So it's not just EXISTS or NOT EXISTS.