select * into m from a where exists(select * from b where a.id=b.id and a.name=b.name)
-- 相同记录 INSERT M SELECT A.* FROM A表 A WHERE EXISTS( SELECT * FROM B表 WHERE A.ID = ID AND A.NAME = NAME)-- 不相同记录 INSERT N SELECT A.* FROM A表 A WHERE NOT EXISTS( SELECT * FROM B表 WHERE A.ID = ID AND A.NAME = NAME) UNION ALL SELECT A.* FROM B表 A WHERE NOT EXISTS( SELECT * FROM A表 WHERE A.ID = ID AND A.NAME = NAME)
declare @ta table(id int ,name varchar(10)) insert @ta select 12,'GOGO' union all select 23,'RIK' union all select 21,'BOB' declare @tb table(id int ,name varchar(10)) insert @tb select 20,'LILY' union all select 21,'BOB' union all select 24,'GIGI'----将相同的行插入到#m表中 select a.* into #m from @ta a inner join @tb b on a.id = b.id and a.name = b.name ----查看#m表 select * from #m----将不同的行插入到#n表中 select a.* into #n from @ta a where not exists(select 1 from #m where id = a.id and name = a.name ) union all select b.* from @tb b where not exists(select 1 from #m where id = b.id and name = b.name ) ----查看#n表 select * from #n----清除临时表 drop table #m,#n
declare @a table (id int,name varchar(10)) declare @b table (id int,name varchar(10)) insert into @a select 12,'GOGO' union all select 23,'RIK' union all select 21,'BOB' insert into @b select 20,'LILY' union all select 21,'BOB' union all select 23,'GIGI'----使用一下语句--相同的记录 select * into #1 from @a a where exists(select * from @b where a.id=id and a.name=name) --不同的记录 select * into #2 from @a a where not exists(select * from @b where a.id=id and a.name=name) select * from #1 select * from #2drop table #1,#2
--学习,再发declare @a table (id int,name varchar(10)) declare @b table (id int,name varchar(10)) insert into @a select 12,'GOGO' union all select 23,'RIK' union all select 21,'BOB' insert into @b select 20,'LILY' union all select 21,'BOB' union all select 23,'GIGI'----使用一下语句--相同的记录 select * into #1 from @a a where exists(select * from @b where a.id=id and a.name=name) --不同的记录 select * into #2 from @a a where not exists(select * from @b where a.id=id and a.name=name) union all select * from @b b where not exists(select * from @a where b.id=id and b.name=name)
INSERT M SELECT A.*
FROM A表 A
WHERE EXISTS(
SELECT * FROM B表
WHERE A.ID = ID
AND A.NAME = NAME)-- 不相同记录
INSERT N
SELECT A.*
FROM A表 A
WHERE NOT EXISTS(
SELECT * FROM B表
WHERE A.ID = ID
AND A.NAME = NAME)
UNION ALL
SELECT A.*
FROM B表 A
WHERE NOT EXISTS(
SELECT * FROM A表
WHERE A.ID = ID
AND A.NAME = NAME)
insert @ta
select 12,'GOGO' union all
select 23,'RIK' union all
select 21,'BOB'
declare @tb table(id int ,name varchar(10))
insert @tb
select 20,'LILY' union all
select 21,'BOB' union all
select 24,'GIGI'----将相同的行插入到#m表中
select a.* into #m from @ta a inner join @tb b on a.id = b.id and a.name = b.name
----查看#m表
select * from #m----将不同的行插入到#n表中
select a.* into #n from @ta a where not exists(select 1 from #m where id = a.id and name = a.name )
union all
select b.* from @tb b where not exists(select 1 from #m where id = b.id and name = b.name )
----查看#n表
select * from #n----清除临时表
drop table #m,#n
declare @b table (id int,name varchar(10))
insert into @a select 12,'GOGO' union all select
23,'RIK' union all select
21,'BOB'
insert into @b select 20,'LILY' union all select
21,'BOB' union all select
23,'GIGI'----使用一下语句--相同的记录
select * into #1 from @a a where exists(select * from @b where a.id=id and a.name=name)
--不同的记录
select * into #2 from @a a where not exists(select * from @b where a.id=id and a.name=name)
select * from #1
select * from #2drop table #1,#2
declare @b table (id int,name varchar(10))
insert into @a select 12,'GOGO' union all select
23,'RIK' union all select
21,'BOB'
insert into @b select 20,'LILY' union all select
21,'BOB' union all select
23,'GIGI'----使用一下语句--相同的记录
select * into #1 from @a a where exists(select * from @b where a.id=id and a.name=name)
--不同的记录
select * into #2 from @a a where not exists(select * from @b where a.id=id and a.name=name)
union all
select * from @b b where not exists(select * from @a where b.id=id and b.name=name)
select * from #1
select * from #2drop table #1,#2