CREATE TABLE [dbo].[t1] (
[num] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[year] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[t2] (
[num] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[year] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOinsert into t1
select '0','name1','2004'
union all select '0','name2','2004'
union all select '0','name3','2004'
insert into t2
select '2','name1','2004'更改:
update A
set A.num=B.num
from t1 A
INNER join t2 B on B.[name]=A.[name]SELECT * FROM T1
num name year
---- ----- ----
2 name1 2004
0 name2 2004
0 name3 2004
[num] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[year] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[t2] (
[num] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[year] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOinsert into t1
select '0','name1','2004'
union all select '0','name2','2004'
union all select '0','name3','2004'
insert into t2
select '2','name1','2004'更改:
update A
set A.num=B.num
from t1 A
INNER join t2 B on B.[name]=A.[name]SELECT * FROM T1
num name year
---- ----- ----
2 name1 2004
0 name2 2004
0 name3 2004
就执行update,
如果不存在,
就执行insert into数据更新后,
select * from t2
要得到的结果集为:
num name year
---- ----- ----
0 name1 2004
0 name2 2004
0 name3 2004
insert into t2 (num,name,year) values(select t1.num ,t1.name,a.year from t1 where t1.name+t1.year not in (select name+year from t2))