34,590
社区成员
发帖
与我相关
我的任务
分享
SELECT * FROM Y
/*
B
001
002
003
*/
SELECT * FROM X
/*
A B
S0001 001
S0002 002
S0002 003
*/
SELECT * FROM X
/*
A B
S0001 001
S0001 002--需要insert进来
S0001 003--需要insert进来
S0002 001--需要insert进来
S0002 002
S0002 003
*/
INSERT INTO Y (A,b)
SELECT t1.A ,
t1.B
FROM ( SELECT Y1.A,
X.B
FROM ( SELECT DISTINCT
A AS A
FROM Y
) AS Y1,X
) t1
WHERE NOT EXISTS ( SELECT 1
FROM Y
WHERE t1.A=Y.A AND t1.B=Y.B)
--A,B全连接
select distinct X.A,Y.B into tbl
--然后把tbl和Y表名换掉
--这样可以不用做集合操作
SELECT DISTINCT A INTO #t FROM Y
INSERT INTO X (A,b)
SELECT X.A,Y.B FROM X,#t Y
EXCEPT
SELECT A,B FROM X