请问这条sql语句应该怎样写?

[guolucky2003]

因为test表里存在一些canpin表没有的记录,需要将canpin表没有的记录插入canpin表
下面是我写的,但不能实现插入功能:
if not exists(select * from canpin a,test b where a.userid=b.userid and a.canpinid=b.canpinid)
begin
insert into canpin_ukq(userid,canpinid,color,price) select userid,canpinid,color,price from test
end

[wangxuelid]

上面呀，，？

[青锋-SS]

--try
insert into canpin_ukq(userid,canpinid,color,price) 
select userid,canpinid,color,price from test b
where canpin_ukq.userid=b.userid and canpin_ukq.canpinid=b.canpinid
end 

[Garnett_KG]

insert into canpin_ukq(userid,canpinid,color,price) 
select a.userid,a.canpinid,a.color,a.price 
from test  a
     left join canpin b on a.userid=b.userid and a.canpinid=b.canpinid
where b.userid is null

[水族杰纶]

insert into canpin_ukq(userid,canpinid,color,price) select userid,canpinid,color,price from test where
not exists(select * from canpin a,test b where a.userid=b.userid and a.canpinid=b.canpinid)

[青锋-SS]

上面弄错了

[青锋-SS]

insert into canpin_ukq(userid,canpinid,color,price) 
select userid,canpinid,color,price from test
where not exists(select 1 from canpin a,test b where a.userid=b.userid and a.canpinid=b.canpinid)