• 主页
• 基础类
• 应用实例
• 新技术前沿

# 兩個表間的插入問題

yxlovemoney 2008-01-03 01:29:40

id score type
1 80 I
2 80 I
3 80 I
NULL NULL NULL
NULL NULL NULL

idd score _type
1 80 I
2 90 C
3 100 M
4 120 N
5 130 P

...全文
155 点赞 收藏 10

10 条回复

changjiangzhibin 2008-01-03
up

azhhuoiu 2008-01-03
INSERT into t1 (select * from t2 where t2.id <>t1.id)

tektite 2008-01-03
``````

create table T1 (id int, score int, [type] char(1))

INSERT into T1 VALUES(1, 80, 'I')
INSERT into T1 VALUES(2, 80, 'I')
INSERT into T1 VALUES(3, 80, 'I')
INSERT into T1 VALUES(NULL, NULL, NULL)
INSERT into T1 VALUES(NULL, NULL, NULL)

create table T2 (idd int,score int, [type] char(1))

insert INTO T2 VALUES(1, 80, 'I')
insert INTO T2 VALUES(2, 90, 'C')
insert INTO T2 VALUES(3, 100,'M')
insert INTO T2 VALUES(4, 120,'N')
insert INTO T2 VALUES(5, 130,'P')

insert into T1
select *  from T2  where not exists(select * from T1  where T1.id=T2.idd)

select * from T1

drop table T1
drop table T2

/*
id          score       type
----------- ----------- ----
1           80          I
2           80          I
3           80          I
NULL        NULL        NULL
NULL        NULL        NULL
4           120         N
5           130         P

(7 行受影响)
*/``````

``````
insert into 表1 select * from 表2 where 表2.id>max(表1.id)

``````

ljking0731 2008-01-03
``````--建表
create table #tab1(id int,score int,type char(2))
create table #tab2(idd int,score int,type char(2))

insert into #tab1
select 1,80,'I' union all
select 2,80,'I' union all
select 3,80,'I' union all
select null,null,null union all
select null,null,null

insert into #tab2
select 1,80,'I' union all
select 2,90,'C' union all
select 3,100,'M' union all
select 4,120,'N' union all
select 5,130,'P'
--插入数据
insert into #tab1
select * from #tab2
where idd not in (select id from #tab1 type where id is not null)
--显示
select * from #tab1 order by id
--结果
id          score       type
----------- ----------- ----
NULL        NULL        NULL
NULL        NULL        NULL
1           80          I
2           80          I
3           80          I
4           120         N
5           130         P

（所影响的行数为 7 行）``````

qiule 2008-01-03
mark

yxlovemoney 2008-01-03

yxlovemoney 2008-01-03

yxlovemoney 2008-01-03

insert into bbb
select * from aaa where not exists(select aaa.id from aaa where bbb.idd=aaa.id)

insert T1
select * from T2 where not exists(select 1 from T1 where ID=t2.ID)

MS-SQL Server

3.2w+

MS-SQL Server相关内容讨论专区