一、复制表定义

假设我们有一个数据表Person,有Id,FirstName,LastName,Weight,Height5个列,表结构可以参考这一篇。现在我们想创建一个新表叫People,表结构和Person一模一样,也就是列名和数据类型都一致。我们可以通过复制Person表的结构而不是复制表中的记录来创建这个表,贴一下sql代码:

1、Sql Server

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

  1. SELECT 
  2.     Id, 
  3.     FirstName, 
  4.     LastName, 
  5.     Weight, 
  6.     Height 
  7.     INTO People 
  8. FROM 
  9.     Person(NOLOCK) 
  10.     WHERE 1=0 



当使用INTO命令来复制表时,查询所返回的行都会加入新生成的表People中(复制表数据见下二),除非我们在WHERE子句条件中定义一个恒为false的条件,即1=0。

还要注意,Person表的Id是自增的主键,但是复制后新建的People表的Id没有变成主键。

2、Oracle、MySql和PostgreSQL

 

  1. CREATE TABLE People 
  2. AS 
  3. SELECT 
  4. Id, 
  5. FirstName, 
  6. LastName, 
  7. Weight, 
  8. Height 
  9. FROM 
  10. Person 
  11. WHERE 1=0 



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

3、DB2
   

  1. CREATE TABLE People LIKE Person 



SELECT什么的都是浮云,就是这么简洁。从这条语句可以认为IBM完胜MS,Oracle…….吗?

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

还是以Person和People表来举例,假如Person和People表都已经存在,把Person表中Id小于10的人复制到People表中:

1、表结构完全相同

   

  1. INSERT INTO People 
  2.     --Id, 
  3.     FirstName, 
  4.     LastName, 
  5.     Weight, 
  6.     Height 
  7. SELECT 
  8.     --Id, 
  9.     FirstName, 
  10.     LastName, 
  11.     Weight, 
  12.     Height 
  13. FROM 
  14.     Person 
  15.     WHERE Id<10 



INSERT INTO语句指定要插入的列,SELECT列的顺序和插入处写的列顺序要一致,如果没有指定则插入所有列。

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

2、People表少一列

假设People少一列Weight,那么通过下面的sql语句

   

  1. INSERT INTO People 
  2.     --Id, 
  3.     FirstName, 
  4.     LastName, 
  5.     --Weight, 
  6.     Height 
  7. SELECT 
  8.     --Id, 
  9.     FirstName, 
  10.     LastName, 
  11.     --Weight, 
  12.     Height 
  13. FROM 
  14.     Person 
  15.     WHERE Id<10 



插入数据正常。

3、People表多一列

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

  1. INSERT INTO People 
  2.     --Id, 
  3.     FirstName, 
  4.     LastName, 
  5.     Weight, 
  6.     Height, 
  7.     HWDiff 
  8. SELECT 
  9.     --Id, 
  10.     FirstName, 
  11.     LastName, 
  12.     Weight, 
  13.     Height, 
  14.     Height-Weight AS HWDiff 
  15. FROM 
  16.     Person 
  17.     WHERE Id<10 



测试也通过了,这是因为float和int类型可以相互转换(之所以说是相互转换,您可以试着将Person表的Height和Weight设计成int型,而People表的对应列设计成float型,然后再复制试试看,都可以的),当然如果把HWDiff直接设计成float型,那最好了。从2和3可以说明,我们向目标表中只要插入的列和选择的列完全一致,表结构不完全一样也可以正常复制。