Please help: What's the Error for Statement: Insert with Not Exists
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.:mad:
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.