合并表
例如已有以下两表
表1
good_id good_bm goods_name
gs001 0001 A
gs002 1001 b
gs005 0002 C
gs006 0003 E
gs010 2001 F
gs014 2002 G
gs015 2003 H
gs016 3001 I
gs020 3002 J
表2
storage_id good_id storage_num
sa001 gs001 10
sa002 gs005 100
sa003 gs006 20
请问如何把表2里不存在的表1的记录添加到表2里
如:表2里没有gs002,就在表2添加一条 sa004 gs002 0
说明good_id和storage_id都是可随机生成的唯一的值
select id=identity(int,101,1),good_id,storage_num=0
into #t from 表a a
where not exists(
select * from 表2 where good_id=a.good_id)
insert 表2
select storage_id='sa'+right(id+right((select max(storage_id) from 表2),3),3)
,good_id,storage_num
from #t
drop table #t
into #t from 表a a
where not exists(
select * from 表2 where good_id=a.good_id)
insert 表2
select storage_id='sa'+right(id+right((select max(storage_id) from 表2),3),3)
,good_id,storage_num
from #t
drop table #t