兩個表間的插入問題

[yxlovemoney]

表１：
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

要怎麼做才可以把表２中的４和５的數據插入表１裡去呢？

[中国风]

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

[yxlovemoney]

好像不行哦，我這樣寫
insert into bbb
select * from aaa where not exists(select aaa.id from aaa where bbb.idd=aaa.id)

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

我去掉了 表名. 還是有錯。

[yxlovemoney]

暈，現在又可以了，謝謝你了！

[yxlovemoney]

牠提示錯誤：無法繫結多重部份（Multi-Part）,識別碼"bbb.idd" 不過有時候確實有這個提示錯誤，為什麼呢？

[qiule]

mark

[ljking0731]

--建表
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 行）

[一者仁心]

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

[tektite]

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 行受影响)
*/

[azhhuoiu]

INSERT into t1 (select * from t2 where t2.id <>t1.id)

[changjiangzhibin]

up