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

兩個表間的插入問題

yxlovemoney 2008-01-03 01:29:40
表1:
id score type
1 80 I
2 80 I
3 80 I
NULL NULL NULL
NULL NULL NULL

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

要怎麼做才可以把表2中的4和5的數據插入表1裡去呢?
...全文
155 点赞 收藏 10
写回复
10 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
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 行受影响)
*/


回复
月下之木 2008-01-03

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
牠提示錯誤:無法繫結多重部份(Multi-Part),識別碼"bbb.idd" 不過有時候確實有這個提示錯誤,為什麼呢?
回复
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)

牠提示錯誤:無法繫結多重部份(Multi-Part),識別碼"bbb.idd"

我去掉了 表名. 還是有錯。
回复
中国风 2008-01-03

insert T1
select * from T2 where not exists(select 1 from T1 where ID=t2.ID)
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告