insert into table1(字段1,字段2,字段3,字段4)select 字段1,字段2,字段3,字段4 from table2 如果是要在一张现有的表插入另一张表的字段,可以把要对考的字段建一张视图,在视图中通过update实在对考,那么现有的这张表的字段也就插入了另一张表的字段!
insert into table1(字段1,字段2,字段3,字段4)select 字段1,字段2,字段3,字段4 from table2
insert into table1(field1,field2,field,field....)select field1,field2,field3,field... from table2语法如上~!!!!
如果表中没有自动编号字段,且结构完全相同,用如下SQL:insert into T1 selec * from T2如果存在自动编号字段,或者结构不完全相同,需要指定字段列表,如下:insert into T1(....) select ... from T2 如果是在当前实例的不同数据库中,还要指定数据库名,如下:insert into T1(....) select ... from 数据库2.dbo.T2
create table t(id int,内容1 varchar(10),内容2 varchar(10)) insert into t select 1,'a1','b1' insert into t select 2,'a2','b2' insert into t select 3,'a3','b3' insert into t select 4,'a4','b4'create table tt(id int,内容1 varchar(10),内容2 varchar(10)) insert into tt select 1,'c1','d1' insert into tt select 2,'c2','d2' insert into tt select 3,'c3','d3' insert into tt select 4,'c4','d4'select * into #t1 from t select * into #t2 from tt truncate table t truncate table ttinsert into t select * from #t2 insert into tt select * from #t1 select * from t select * from tt drop table t drop table tt drop table #t1 drop table #t2id 内容1 内容21 c1 d1 2 c2 d2 3 c3 d3 4 c4 d4 (t表) id 内容1 内容21 a1 b1 2 a2 b2 3 a3 b3 4 a4 b4(tt表)
selec *
from T2如果存在自动编号字段,或者结构不完全相同,需要指定字段列表,如下:insert into T1(....)
select ...
from T2
如果是在当前实例的不同数据库中,还要指定数据库名,如下:insert into T1(....)
select ...
from 数据库2.dbo.T2
insert into t select 1,'a1','b1'
insert into t select 2,'a2','b2'
insert into t select 3,'a3','b3'
insert into t select 4,'a4','b4'create table tt(id int,内容1 varchar(10),内容2 varchar(10))
insert into tt select 1,'c1','d1'
insert into tt select 2,'c2','d2'
insert into tt select 3,'c3','d3'
insert into tt select 4,'c4','d4'select * into #t1 from t
select * into #t2 from tt truncate table t
truncate table ttinsert into t select * from #t2
insert into tt select * from #t1
select * from t
select * from tt
drop table t
drop table tt
drop table #t1
drop table #t2id 内容1 内容21 c1 d1
2 c2 d2
3 c3 d3
4 c4 d4
(t表)
id 内容1 内容21 a1 b1
2 a2 b2
3 a3 b3
4 a4 b4(tt表)