insert T1 select 字段1 from T2 where not exists(select 1 from T1 where t1.字段1=t2.字段2)
insert T1 select distinct 字段1 from T2 where not exists(select 1 from T1 where t1.字段1=t2.字段2)
insert T1 (字段1)select DISTINCT 字段1 from T2 where 字段1 not IN (select 字段1 from T1)
---------------------------------------------------------------- -- Author :SQL77(只为思齐老) -- Date :2010-01-10 10:22:11 -- Version: -- Microsoft SQL Server 2000 - 8.00.194 (Intel X86) -- Aug 6 2000 00:57:48 -- Copyright (c) 1988-2000 Microsoft Corporation -- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[TB1] if object_id('[TB1]') is not null drop table [TB1] go create table [TB1]([COL] varchar(1)) insert [TB1] select 'A' union all select 'B' union all select 'C' --> 测试数据:[TB2] if object_id('[TB2]') is not null drop table [TB2] go create table [TB2]([COL] varchar(1)) insert [TB2] select 'A' union all select 'B' union all select 'B' union all select 'C' union all select 'D' union all select 'D' union all select 'E' --------------开始查询-------------------------- INSERT TB1 SELECT DISTINCT * FROM TB2 WHERE COL NOT IN(SELECT COL FROM TB1) select * from [TB1] --select * from [TB2] ----------------结果---------------------------- /* (所影响的行数为 3 行) (所影响的行数为 7 行) (所影响的行数为 2 行)COL ---- A B C D E(所影响的行数为 5 行) */
select 字段1 from T2 where not exists(select 1 from T1 where t1.字段1=t2.字段2)
select distinct 字段1
from T2
where not exists(select 1 from T1 where t1.字段1=t2.字段2)
字段1
from T2
where 字段1 not IN (select 字段1 from T1)
-- Author :SQL77(只为思齐老)
-- Date :2010-01-10 10:22:11
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[TB1]
if object_id('[TB1]') is not null drop table [TB1]
go
create table [TB1]([COL] varchar(1))
insert [TB1]
select 'A' union all
select 'B' union all
select 'C'
--> 测试数据:[TB2]
if object_id('[TB2]') is not null drop table [TB2]
go
create table [TB2]([COL] varchar(1))
insert [TB2]
select 'A' union all
select 'B' union all
select 'B' union all
select 'C' union all
select 'D' union all
select 'D' union all
select 'E'
--------------开始查询--------------------------
INSERT TB1 SELECT DISTINCT * FROM TB2 WHERE COL NOT IN(SELECT COL FROM TB1)
select * from [TB1]
--select * from [TB2]
----------------结果----------------------------
/* (所影响的行数为 3 行)
(所影响的行数为 7 行)
(所影响的行数为 2 行)COL
----
A
B
C
D
E(所影响的行数为 5 行)
*/
表T1: 表T2:
字段1 字段2 字段1 字段2
A1 A2 A1 A2
B1 B2 A1 A2
CC1 C2 B1 B2
B1 B2
C1 C2
D1 D2
D1 D2
E1 E2
执行结果为:
表T1: 表T2:
字段1 字段2 字段1 字段2
A1 A2 A1 A2
B1 B2 A1 A2
C1 C2 B1 B2
D1 D2 B1 B2
E1 E2 C1 C2
D1 D2
D1 D2
E1 E2
SELECT DISTINCT * FROM T2 WHERE 字段1+字段2 NOT IN(SELECT 字段1+字段2 FROM TB1)