- select * into destTbl from srcTbl
- insert into destTbl(fld1, fld2) select fld1, 5 from srcTbl
select * into destTbl from srcTbl
insert into destTbl(fld1, fld2) select fld1, 5 from srcTbl
以上两句都是将 srcTbl 的数据插入到 destTbl,但两句又有区别的。
第一句(select into from)要求目标表(destTbl)不存在,因为在插入时会自动创建。
第二句(insert into select from)要求目标表(destTbl)存在,由于目标表已经存在,所以我们除了插入源表(srcTbl)的字段外,还可以插入常量,如例中的:5。
第一句(select into from)要求目标表(destTbl)不存在,因为在插入时会自动创建。
第二句(insert into select from)要求目标表(destTbl)存在,由于目标表已经存在,所以我们除了插入源表(srcTbl)的字段外,还可以插入常量,如例中的:5。
一:SELECT INTO FROM语句
语句形式为: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
语句形式为: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
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 INTO FROM语句创建表UserInfo2并复制数据
select u_id,u_name,u_pwd INTO UserInfo2 from UserInfo
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必须存在
select * from UserInfo
select * from UserInfo2
GO
二:INSERT INTO SELECT
语句形式为:Insert into Table2(field1,field2,...) select value1,value2,... from Table1
前提表Table2必须存在
--1.创建测试表
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
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
转载于:https://blog.51cto.com/01258/401435