insert into log([name],[type]) select [name],[type] from file a where not exists
(select 1 from log where [name]=a.[name] and [type]=a.[type])
(select 1 from log where [name]=a.[name] and [type]=a.[type])
调试欢乐多
insert into [log] select 2,22,'33.rar'
create table [file]([name] int,[type] int,[file] varchar(10))
insert into [file] select 2,22,'33.rar'
insert into [file] select 3,33,'44.rar'
insert into [log]([name],[type]) select [name],[type] from [file] a where not exists
(select 1 from [log] where [name]=a.[name] and [type]=a.[type])
select * from [log]
go
drop table [log],[file]
/*
name type file
----------- ----------- ----------
2 22 33.rar
3 33 NULL
*/
--建立测试环境
Use Tempdb
create table tb1(name int,type int,[file] varchar(100))
create table tb2(name int,type int,[file] varchar(100))insert into tb1
select 2,22,'33.rar' union all
select 1,11,'22.rar' insert into tb2
select 2,22,'33.rar' union all
select 4,44,'44.rar' union all
select 5,55,'55.rar' -- 把tb2表的多的数据数据插入到tb1,
insert into tb1(name,type,[file])
select name,Type,'' from
(
select name,type,[FILE] from tb2
except
select name,type,[FILE] from tb1
) A select * from tb1name type file
2 22 33.rar
1 11 22.rar
4 44
5 55 --删除测试环境
drop table tb1
drop table tb2
IF OBJECT_ID('LI1') IS NOT NULL
DROP TABLE LI1
IF OBJECT_ID('LI2') IS NOT NULL
DROP TABLE LI2 create table LI1(name int,type int,[file] varchar(100))
create table LI2(name int,type int,[file] varchar(100)) insert into LI1
select 2,22,'33.rar' union all
select 1,11,'22.rar' insert into LI2
select 2,22,'33.rar' union all
select 4,44,'44.rar' union all
select 5,55,'55.rar'
--执行操作
INSERT INTO LI1([NAME],[TYPE],[FILE])
SELECT S.[NAME],S.[TYPE],''
FROM LI2 S
WHERE CHECKSUM(*)
NOT IN (SELECT CHECKSUM(*) FROM LI1) SELECT * FROM LI1--删除测试环境
DROP TABLE LI1
DROP TABLE LI2 /*结果
name type file
2 22 33.rar
1 11 22.rar
4 44
5 55
/*