二、实例应用

　　1、说明：复制表

　　法一：select * into b from a where 1<>1(仅用于SQlServer)

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 --> 测试数据：[a] if object_id('[a]') is not null drop table [a] go create table [a]([ID] int) insert [a] select 1 union all select 1 union all select 2 union all select 3 union all select null select * from a /* (5 行受影响) ID ----------- 1 1 2 3 NULL   (5 行受影响) */   --只复制表结构 select * into b from a where 1<>1 select * from b /* ID -----------   (0 行受影响)   */

　　法二：select top 0 * into b from a

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 --> 测试数据：[a] if object_id('[a]') is not null drop table [a] go create table [a]([ID] int) insert [a] select 1 union all select 1 union all select 2 union all select 3 union all select null select * from a /* (5 行受影响) ID ----------- 1 1 2 3 NULL   (5 行受影响) */   --只复制表结构 select top 0 * into b from a select * from b /* ID -----------   (0 行受影响)   */

　　2、说明：拷贝表(拷贝数据,源表名：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 --> 测试数据：[a] if object_id('[a]') is not null drop table [a] go create table [a]([ID] int) insert [a] select 1 union all select 1 union all select 2 union all select 3 union all select null select * from a /* (5 行受影响) ID ----------- 1 1 2 3 NULL   (5 行受影响) */   --复制表数据 create table [b]([ID] int) insert into b(id) select id from a select * from b /* ID ----------- 1 1 2 3 NULL   (5 行受影响) */   3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径)

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 --> 测试数据：[a] if object_id('[a]') is not null drop table [a] go create table [a]([ID] int) insert [a] select 1 union all select 1 union all select 2 union all select 3 union all select null select * from a /* (5 行受影响) ID ----------- 1 1 2 3 NULL   (5 行受影响) */   --复制表数据 create table [b]([ID] int) insert into b(id) select id from cc_jz.dbo.a select * from b /* ID ----------- 1 1 2 3 NULL   (5 行受影响) */

　　4、说明：子查询(表名1：a 表名2：b)

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 --> 测试数据：[a] if object_id('[a]') is not null drop table [a] go create table [a]([ID] int)