insert into table2 select * from table1 where checksum(*) not in(select checksum(*) from table2)
insert into tb2 select * from tb1 a where not exists(select 1 from tb2 where id = a.id)
insert into tb2 select * from tb1 a where not exists(select 1 from tb2 where id = a.id and field = a.field )
insert 表2 select a.ID,a.field from 表1 as a left join 表2 as b on a.ID=b.ID and a.field=b.field where b.ID is null
declare @t1 table (id int,field varchar(5)) insert into @t1 select 1,'记录1' union all select 2,'记录2' union all select 1,'记录2' union all select 2,'记录1' declare @t2 table(id int,field varchar(5)) insert into @t2 select 1,'记录2' union all select 2,'记录1' insert into @t2 select * from @t1 a where not exists(select * from @t2 where a.id=id and a.field=field) select * from @t21 记录2 2 记录1 1 记录1 2 记录2
insert into 表2 select * from 表1 a where not exists(select * from 表2 where a.id=id and a.field=field)
declare @t1 table (id int,field nvarchar(5)) insert into @t1 select 1,N'记录1' union all select 2,N'记录2' union all select 1,N'记录2' union all select 2,N'记录1' declare @t2 table(id int,field Nvarchar(5)) insert into @t2 select 1,N'记录2' union all select 2,N'记录1' insert into @t2 select * from @t1 where checksum(*) not in(select checksum(*) from @t2) select * from @t2 /*id field ----------- ----- 1 记录2 2 记录1 1 记录1 2 记录2 */
CREATE TABLE #A (ID VARCHAR(10),S_NAME VARCHAR(10)) INSERT INTO #A SELECT 1 ,'Record1' INSERT INTO #A SELECT 2 ,'Record2' INSERT INTO #A SELECT 1, 'Record2' INSERT INTO #A SELECT 2 ,'Record1' CREATE TABLE #B (ID VARCHAR(10),S_NAME VARCHAR(10))INSERT INTO #B SELECT 1, 'Record2' INSERT INTO #B SELECT 2 ,'Record1'SELECT * FROM #BINSERT INTO #B(ID,S_NAME) SELECT ID,S_NAME FROM #A WHERE ID+S_NAME NOT IN (SELECT ID+S_NAME FROM #B)SELECT * FROM #B DROP TABLE #A,#B/*** 1 Record2 2 Record1/** 1 Record2 2 Record1 1 Record1 2 Record2
--> --> (Andy)生成测试数据 2008-11-05 Set Nocount On declare @1 table([ID] int,[field] nvarchar(3)) Insert @1 select 1,N'记录1' union all select 2,N'记录2' union all select 1,N'记录2' union all select 2,N'记录1'
declare @2 table([ID] int,[field] nvarchar(3)) Insert @2 select 1,N'记录2' union all select 2,N'记录1'Insert Into @2 Select id,field From @1 Except Select id,field From @2Select * From @2 /* ID field ----------- ----- 1 记录2 2 记录1 1 记录1 2 记录2 */
select * from table1
where checksum(*) not in(select checksum(*) from table2)
select *
from tb1 a
where not exists(select 1 from tb2 where id = a.id)
select *
from tb1 a
where not exists(select 1 from tb2 where id = a.id and field = a.field )
insert 表2
select a.ID,a.field from 表1 as a left join 表2 as b on a.ID=b.ID and a.field=b.field
where b.ID is null
declare @t1 table (id int,field varchar(5))
insert into @t1 select 1,'记录1'
union all select 2,'记录2'
union all select 1,'记录2'
union all select 2,'记录1'
declare @t2 table(id int,field varchar(5))
insert into @t2 select 1,'记录2'
union all select 2,'记录1'
insert into @t2
select * from @t1 a where not exists(select * from @t2 where a.id=id and a.field=field)
select * from @t21 记录2
2 记录1
1 记录1
2 记录2
insert into 表2
select * from 表1 a where not exists(select * from 表2 where a.id=id and a.field=field)
insert into @t1 select 1,N'记录1'
union all select 2,N'记录2'
union all select 1,N'记录2'
union all select 2,N'记录1'
declare @t2 table(id int,field Nvarchar(5))
insert into @t2 select 1,N'记录2'
union all select 2,N'记录1'
insert into @t2 select * from @t1 where checksum(*) not in(select checksum(*) from @t2)
select * from @t2
/*id field
----------- -----
1 记录2
2 记录1
1 记录1
2 记录2
*/
INSERT INTO #A SELECT 1 ,'Record1'
INSERT INTO #A SELECT 2 ,'Record2'
INSERT INTO #A SELECT 1, 'Record2'
INSERT INTO #A SELECT 2 ,'Record1'
CREATE TABLE #B (ID VARCHAR(10),S_NAME VARCHAR(10))INSERT INTO #B SELECT 1, 'Record2'
INSERT INTO #B SELECT 2 ,'Record1'SELECT * FROM #BINSERT INTO #B(ID,S_NAME)
SELECT ID,S_NAME
FROM #A
WHERE ID+S_NAME NOT IN (SELECT ID+S_NAME FROM #B)SELECT * FROM #B
DROP TABLE #A,#B/***
1 Record2
2 Record1/**
1 Record2
2 Record1
1 Record1
2 Record2
Set Nocount On
declare @1 table([ID] int,[field] nvarchar(3))
Insert @1
select 1,N'记录1' union all
select 2,N'记录2' union all
select 1,N'记录2' union all
select 2,N'记录1'
declare @2 table([ID] int,[field] nvarchar(3))
Insert @2
select 1,N'记录2' union all
select 2,N'记录1'Insert Into @2
Select id,field From @1 Except Select id,field From @2Select * From @2
/*
ID field
----------- -----
1 记录2
2 记录1
1 记录1
2 记录2
*/