- select * into destTbl from srcTbl
- insert into destTbl(fld1, fld2) select fld1, 5 from srcTbl
第一句(select into from)要求目标表(destTbl)不存在,因为在插入时会自动创建。
第二句(insert into select from)要求目标表(destTbl)存在,由于目标表已经存在,所以我们除了插入源表(srcTbl)的字段外,还可以插入常量,如例中的:5。
语句形式为:SELECT vale1, value2 into Table2 from Table1
前提表Table2不存在,因为在插入时会自动创建表Table2,并将Table1中指定字段数据复制到Table2中
--①.创建表UserInfo
create TABLE UserInfo
(
u_id int,
u_name varchar(10),
u_pwd varchar(10),
CONSTRAINT [PK_UserInfo] PRIMARY KEY CLUSTERED
(
u_id ASC
)
) ON [PRIMARY]
GO
Insert into UserInfo values(1,'aa','show')
Insert into UserInfo values(2,'bb','ming')
Insert into UserInfo values(3,'cc','geng')
Insert into UserInfo values(4,'dd','fan')
GO
select u_id,u_name,u_pwd INTO UserInfo2 from UserInfo
GO
select * from UserInfo
select * from UserInfo2
GO
二:INSERT INTO SELECT
语句形式为:Insert into Table2(field1,field2,...) select value1,value2,... from Table1
前提表Table2必须存在
create TABLE stuInfo
(
u_id int,
u_name varchar(10),
u_pwd varchar(10),
CONSTRAINT [PK_stuInfo] PRIMARY KEY CLUSTERED
(
u_id ASC
)
) ON [PRIMARY]
GO
create TABLE stuInfo2
(
u_id int,
u_name varchar(10),
u_pwd varchar(10),
CONSTRAINT [PK_stuInfo2] PRIMARY KEY CLUSTERED
(
u_id ASC
)
) ON [PRIMARY]
GO
--2.创建测试数据
Insert into stuInfo values(1,'aa','show')
Insert into stuInfo values(2,'bb','ming')
Insert into stuInfo values(3,'cc','geng')
Insert into stuInfo values(4,'dd','fan')
GO
--3.INSERT INTO SELECT语句复制表数据
Insert into stuInfo2(u_id, u_name, u_pwd) select u_id, u_name, u_pwd from stuInfo
文章出处:飞诺网(www.firnow.com):http://dev.firnow.com/course/7_databases/oracle/oraclejs/20100721/482741.html