sql语句复制表定义及复制数据行(多数据库支持)

发布时间:2020-12-30编辑:脚本学堂
分享一些复制表结构与复制表数据的sql语句,包括sql server、oracle、mysql等常用数据库中复制表结构与数据的sql语句示例,还可以实现从一个表向另外的表复制行。

sql语句复制表定义及复制数据行

一、复制表定义

有一个数据表Person,有Id,FirstName,LastName,Weight,Height5个列。
要创建一个新表叫People,表结构和Person一模一样,也就是列名和数据类型都一致。

可以通过复制Person表的结构而不是复制表中的记录来创建这个表。

1、sql server数据库
sql server使用带有不返回任何行的查询和INTO子句:
 

复制代码 代码示例:
SELECT 
Id, 
FirstName, 
LastName, 
Weight, 
Height 
INTO People 
FROM 
Person(NOLOCK) 
WHERE 1=0 

 

等价于
SELECT * INTO People FROM Person WHERE 1=0 


当使用INTO命令来复制表时,查询所返回的行都会加入新生成的表People中(复制表数据见下二),除非我们在WHERE子句条件中定义一个恒为false的条件,即1=0。
注意,Person表的Id是自增的主键,但是复制后新建的People表的Id没有变成主键。

2、oracle、mysql和postgresql
 

复制代码 代码示例:
CREATE TABLE People  
AS
SELECT 
Id, 
FirstName, 
LastName, 
Weight, 
Height 
FROM 
Person 
WHERE 1=0 
 

查询部分以及查询条件和Sql Server蛮像的。

3、db2数据库
 

create table people like person 

二、从一个表向另外的表复制行

还是以person和people表来举例,假如person和people表都已存在,把person表中id小于10的人复制到people表中:

1、表结构完全相同
 

复制代码 代码示例:
insert into people 

--Id, 
FirstName, 
LastName, 
Weight, 
Height 

SELECT  
--Id, 
FirstName, 
LastName, 
Weight, 
Height 
FROM 
Person 
WHERE Id<10

insert into语句指定要插入的列,select列的顺序和插入处写的列顺序要一致,如果没有指定则插入所有列,如下:
 

insert into people select * from person where id<10 

注意,当把主键也选择时,Sql Server提示“当 IDENTITY_INSERT 设置为 OFF 时,不能为表 'People' 中的标识列插入显式值”。

2、people表少一列
假设people少一列weight,sql语句:
 

复制代码 代码示例:
insert into people 

--Id, 
FirstName, 
LastName, 
--Weight, 
Height 

SELECT 
--Id, 
FirstName, 
LastName, 
--Weight, 
Height 
FROM 
Person 
WHERE Id<10 
 

插入数据正常。

3、people表多一列

假设people表多一列,用身高减去体重,叫做hwdiff,身高和体重是float类型,现在把hwdiff设计成int型,然后执行复制:
 

复制代码 代码示例:
insert into people 

--Id, 
FirstName, 
LastName, 
Weight, 
Height, 
HWDiff 

SELECT 
--Id, 
FirstName, 
LastName, 
Weight, 
Height, 
Height-Weight AS HWDiff 
FROM 
Person 
WHERE Id<10 

测试通过,这是因为float和int类型可以相互转换(说到相互转换,可以试着将Person表的Height和Weight设计成int型,而People表的对应列设计成float型,然后再复制试试看,都可以的),当然如果把HWDiff直接设计成float型,那最好了。

从2和3可以说明,向目标表中只要插入的列和选择的列完全一致,表结构不完全一样也可以正常复制。