怎么进行比较生成?

[enasp]

怎么进行比较生成?

表一：log

name type file
2 22 33.rar
3 33


表二：file
name type file
2 22 33.rar
3 33 44.rar


计算当表二与表一对比后，如果有完整的记录一样，就不再更新，如果发现没有3这条记录，自动在表一生成一条，但只能是更新前两个字段， file为空

[-晴天]

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])

[-晴天]

create table [log]([name] int,[type] int,[file] varchar(10))
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
*/

[--小F--]

牛人速度快

[ssxw]

--建立测试环境
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 tb1

name type file
2 22 33.rar
1 11 22.rar
4 44
5 55

--删除测试环境
drop table tb1
drop table tb2

[JonasFeng]

 
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	
/*