--建立测试环境
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test]
GO
CREATE TABLE [dbo].[test] (
[id] [varchar] (10),
[t1] [int] NULL,
[t2] [varchar] (10),
[t3] [varchar] (10)
) ON [PRIMARY]
GO
insert into test
select 'a1',1,'x1','aa' union all
select 'a1',2,'b2','1b' union all
select 'a2',3,'c3','3c' union all
select 'a2',4,'q4','1d'
--建立另一个表,与其实表结构一样
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CopyTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CopyTest]
select top 0 * into CopyTest from test
insert into CopyTest
select 'a1',1,'x1','aa' union all
select 'a1',2,'b2','1b' union all
select 'a22',3,'c3','3c' union all
select 'a23',4,'q4','1d'
--测试
--查询不相等,并插入到另一个表里
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OtherTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[OtherTest]
select top 0 * into OtherTest from test
insert into OtherTest
select * from test A
where checksum(*) not in(select checksum(*) from CopyTest )
--显示结果
select * from OtherTest
/*
id ti t2 t3
a2 3 c3 3c
a2 4 q4 1d
*/
--删除测试环境
drop table test
drop table CopyTest
drop table OtherTest