SQLServer基礎語法實例應用（二）

　　二、實例應用

　　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)