大概的语句: insert into D(d1,d2,d3,d4,d5,d6,d7) select a.a1 as d1,a.a2 as d2,a.a3 as d3,a.a4 as d4, b.b2 as d5,b.b3 as d6,c.c2 as d7 from a left join b on a.a1 = b.b1 left join c on b.b2 = c.c1
存储过程:create proc dbo.proc_merge_abc as insert into D(d1,d2,d3,d4,d5,d6,d7) select a.a1 as d1,a.a2 as d2,a.a3 as d3,a.a4 as d4, b.b2 as d5,b.b3 as d6,c.c2 as d7 from a left join b on a.a1 = b.b1 left join c on b.b2 = c.c1go
CREATE PROC test AS INSERT INTO d ( d1 , d2 , d3 , d4 , d5 , d6 , d7 ) SELECT a1 , a2 , a3 , a4 , b1 , b2 , b3 , c1 , c2 FROM a LEFT JOIN b ON a.a1 = b.b1 --这里看你的业务是要用到Inner join 还是left join了 LEFT JOIN c ON b.b2 = c.c1 --这里看你的业务是要用到Inner join 还是left join了
你这个用union应该也是可以实现的
merge into?例子:--创建测试表 CREATE TABLE t_A (MCLASS VARCHAR(20),SNO VARCHAR(20)) INSERT INTO t_A SELECT 'L-1','123' UNION ALL SELECT 'L-2','234' UNION ALL SELECT 'L-3','345'CREATE TABLE t_B (MCLASS VARCHAR(20),SNO VARCHAR(20)) INSERT INTO t_B SELECT 'L-1','999' UNION ALL SELECT 'L-2','999' UNION ALL SELECT 'L-4','888'--确定目标表 merge into t_A as a --从t_B表中查找MCLASS相同的数据 using t_B as b on a.MCLASS=b.MCLASS --当MCLASS相同时,则更新t_A中SNO字段 when Matched then update set a.SNO=b.SNO --当t_A表中不存在MCLASS相同的数据时,则从t_B表插入目标表t_A when Not Matched then Insert(MCLASS,SNO) values(b.MCLASS,b.SNO);select * from t_A select * from t_Bdrop table t_A drop table t_B更新后的t_a MCLASS SNO -------------------- -------------------- L-1 999 L-2 999 L-3 345 L-4 888(4 行受影响)
insert into D(d1,d2,d3,d4,d5,d6,d7)
select a.a1 as d1,a.a2 as d2,a.a3 as d3,a.a4 as d4,
b.b2 as d5,b.b3 as d6,c.c2 as d7
from a
left join b
on a.a1 = b.b1
left join c
on b.b2 = c.c1
as
insert into D(d1,d2,d3,d4,d5,d6,d7)
select a.a1 as d1,a.a2 as d2,a.a3 as d3,a.a4 as d4,
b.b2 as d5,b.b3 as d6,c.c2 as d7
from a
left join b
on a.a1 = b.b1
left join c
on b.b2 = c.c1go
AS
INSERT INTO d
( d1 ,
d2 ,
d3 ,
d4 ,
d5 ,
d6 ,
d7
)
SELECT a1 ,
a2 ,
a3 ,
a4 ,
b1 ,
b2 ,
b3 ,
c1 ,
c2
FROM a
LEFT JOIN b ON a.a1 = b.b1 --这里看你的业务是要用到Inner join 还是left join了
LEFT JOIN c ON b.b2 = c.c1 --这里看你的业务是要用到Inner join 还是left join了
CREATE TABLE t_A
(MCLASS VARCHAR(20),SNO VARCHAR(20))
INSERT INTO t_A
SELECT 'L-1','123'
UNION ALL
SELECT 'L-2','234'
UNION ALL
SELECT 'L-3','345'CREATE TABLE t_B
(MCLASS VARCHAR(20),SNO VARCHAR(20))
INSERT INTO t_B
SELECT 'L-1','999'
UNION ALL
SELECT 'L-2','999'
UNION ALL
SELECT 'L-4','888'--确定目标表
merge into t_A as a
--从t_B表中查找MCLASS相同的数据
using t_B as b on a.MCLASS=b.MCLASS
--当MCLASS相同时,则更新t_A中SNO字段
when Matched then update set a.SNO=b.SNO
--当t_A表中不存在MCLASS相同的数据时,则从t_B表插入目标表t_A
when Not Matched then Insert(MCLASS,SNO) values(b.MCLASS,b.SNO);select * from t_A
select * from t_Bdrop table t_A
drop table t_B更新后的t_a
MCLASS SNO
-------------------- --------------------
L-1 999
L-2 999
L-3 345
L-4 888(4 行受影响)