求教高手一条SQL文

有结构相同的三章表A表,B表,C表

A: 列1 列2 列3 列4
  1 b1 c1 d1
  3 b2 c2 d2
  4 b2 c2 d3
  7 b3 c3 d4
  2 b4 c4 d5
  6 b4 c4 d6
  5 b5 c5 d7

B: 列1 列2 列3 列4
  1 b2 c2 m1
  2 b2 c2 m2
  3 b4 c4 m3
  4 b4 c4 m4
  5 b5 c5 m5

已以下规则生成c表: 条件:A.列2=B.列2 并且 A.列3=B.列3, 满足这个条件的话把B表中的数据往c表中插入,
不满足的话把A表中的数据往C表中插入, 注意 B表中的数据往C表中插入时 「列1」要用A表中的数据

C:
  列1 列2 列3 列4
  1 b1 c1 d1
  3 b2 c2 m1
  4 b2 c2 m2
  7 b3 c3 d4
  2 b4 c4 m3
  6 b4 c4 m4
  5 b5 c5 m5

不一定要1条SQL文,因为时写存储过程,所以实现就可以了。

结果有误.
  3 b2 c2 d2
  4 b2 c2 d3

  1 b2 c2 m1
  2 b2 c2 m2

满足楼主条件的,不仅
  1 b1 c1 d1
  3 b2 c2 m1
还有
  3 b1 c1 d1
  1 b2 c2 m1

复制岔了;
结果有误.
  3 b2 c2 d2
  4 b2 c2 d3

  1 b2 c2 m1
  2 b2 c2 m2

满足楼主条件的,不仅
  3 b2 c2 m1
  4 b2 c2 m2
还有
  3 b2 c2 m2
  4 b2 c2 m1

SQL code

create table t1(c1 int,c2 varchar(10),c3 varchar(10),c4 varchar(10))
insert into t1 select 1,'b1','c1','d1'
insert into t1 select 3,'b2','c2','d2'
insert into t1 select 4,'b2','c2','d3'
insert into t1 select 7,'b3','c3','d4'
insert into t1 select 2,'b4','c4','d5'
insert into t1 select 6,'b4','c4','d6'
insert into t1 select 5,'b5','c5','d7'
create table t2(c1 int,c2 varchar(10),c3 varchar(10),c4 varchar(10))
insert into t2 select 1,'b2','c2','m1'
insert into t2 select 2,'b2','c2','m2'
insert into t2 select 3,'b4','c4','m3'
insert into t2 select 4,'b4','c4','m4'
insert into t2 select 5,'b5','c5','m5'
create table t3(c1 int,c2 varchar(10),c3 varchar(10),c4 varchar(10))
go
insert into t3
select a.c1,a.c2,a.c3,b.c4 from t1 a inner join t2 b on a.c2=b.c2 and a.c3=b.c3
union all
select * from t1 a where not exists(select 1 from t2 where c2=a.c2 and c3=a.c3) order by c1
go
select * from t3
/*
c1          c2         c3         c4
----------- ---------- ---------- ----------
1           b1         c1         d1
2           b4         c4         m3
2           b4         c4         m4
3           b2         c2         m1
3           b2         c2         m2
4           b2         c2         m1
4           b2         c2         m2
5           b5         c5         m5
6           b4         c4         m3
6           b4         c4         m4
7           b3         c3         d4

(11 行受影响)

*/
go
drop table t1,t2,t3

SQL code

use Tempdb
go
--> --> 
 
if not object_id(N'A') is null
    drop table A
Go
Create table A([列1] int,[列2] nvarchar(2),[列3] nvarchar(2),[列4] nvarchar(2))
Insert A
select 1,N'b1',N'c1',N'd1' union all
select 3,N'b2',N'c2',N'd2' union all
select 4,N'b2',N'c2',N'd3' union all
select 7,N'b3',N'c3',N'd4' union all
select 2,N'b4',N'c4',N'd5' union all
select 6,N'b4',N'c4',N'd6' union all
select 5,N'b5',N'c5',N'd7'
Go

 
if not object_id(N'B') is null
    drop table B
Go
Create table B([列1] int,[列2] nvarchar(2),[列3] nvarchar(2),[列4] nvarchar(2))
Insert B
select 1,N'b2',N'c2',N'm1' union all
select 2,N'b2',N'c2',N'm2' union all
select 3,N'b4',N'c4',N'm3' union all
select 4,N'b4',N'c4',N'm4' union all
select 5,N'b5',N'c5',N'm5'
Go
--> --> 
 
if not object_id(N'C') is null
    drop table C
Go
Create table C([列1] int,[列2] nvarchar(2),[列3] nvarchar(2),[列4] nvarchar(2))
GO


CREATE PROC P
AS

;WITH Cte
AS
(
SELECT [列1],[列2],[列3],[列4]
FROM 
(SELECT  a.[列1],b.[列2],b.[列3],b.[列4],ROW_NUMBER()OVER(PARTITION BY a.[列1] ORDER BY b.[列1] ) AS row
FROM B INNER JOIN A ON A.[列2]=B.[列2] AND A.[列3]=B.[列3]
)t
WHERE row=1
UNION ALL
SELECT A.* FROM A WHERE NOT EXISTS(SELECT 1 FROM B WHERE [列2]=A.[列2] AND [列3]=A.[列3])

)
INSERT INTO C
SELECT * FROM Cte AS a WHERE NOT EXISTS(SELECT 1 FROM C WHERE [列2]=A.[列2] AND [列3]=A.[列3])
go

加新增到C時的排序
SQL code

Create PROC P
AS

;WITH Cte
AS
(
SELECT [列1],[列2],[列3],[列4]
FROM 
(SELECT  a.[列1],b.[列2],b.[列3],b.[列4],ROW_NUMBER()OVER(PARTITION BY a.[列1] ORDER BY b.[列1] ) AS row
FROM B INNER JOIN A ON A.[列2]=B.[列2] AND A.[列3]=B.[列3]
)t
WHERE row=1
UNION ALL
SELECT A.* FROM A WHERE NOT EXISTS(SELECT 1 FROM B WHERE [列2]=A.[列2] AND [列3]=A.[列3])

)
INSERT INTO C
SELECT * FROM Cte AS a WHERE NOT EXISTS(SELECT 1 FROM C WHERE [列2]=A.[列2] AND [列3]=A.[列3]) ORDER BY [列1]
GO
EXEC P
go
SELECT * FROM C 
/*
列1    列2    列3    列4
1    b1    c1    d1
2    b4    c4    m3
3    b2    c2    m1
4    b2    c2    m1
5    b5    c5    m5
6    b4    c4    m3
7    b3    c3    d4
*/