如何使用 SELECT INTO 複製表，但忽略 IDENTITY 屬性

2024-05-27

我們在撰寫TSQL時常常會需要建立跟實體資料表一樣資料欄位的暫存表來暫存及處理資料。最常用的方法就是Select Into來做，省去撰寫一堆Create的語法。

基本上複製一個空表作法如下程式碼

Create Table tb1(id int identity,cname nvarchar(10));
  GO
  Insert Into tb1(cname) Values(N'Rock');
  --利用Select Into 來產生一個暫存表#tb1其格式同時體表tb1
  Select * Into #tb1 From tb1 Where 1=0;
  --比較兩表
  Select * From tb1;
  Select * From #tb1;

如上圖中我們已成功建立一張暫存表叫#tb1，其格式跟實體表tb1完全一樣。

接下來我們模擬要將tb1過濾完的資料寫入到#tb1中，但此時卻發生了Identity Insert Error的問題，如下圖所示。

由於我們採用Select Into 來產出暫存表，因此該資料表的欄位屬性也一併複製了過來，所以該#tb1的id欄位有Identity屬性，導致寫入失敗。

那我該如何用Select Into複製表的時候讓id這欄位不要用Identity的屬性呢?

微軟官方文件針對這問題還真有說明，其表列下面方法。

When an existing identity column is selected into a new table, the new column inherits the IDENTITY property,
unless one of the following conditions is true:
* The SELECT statement contains a join, GROUP BY clause, or aggregate function.
* Multiple SELECT statements are joined by using UNION.
* The identity column is listed more than one time in the select list.
* The identity column is part of an expression.
* The identity column is from a remote data source.
If any one of these conditions is true, the column is created NOT NULL instead of inheriting the IDENTITY property.
If an identity column is required in the new table but such a column is not available, or you want a seed or increment
value that is different than the source identity column, define the column in the select list using the IDENTITY function.
See "Creating an identity column using the IDENTITY function" in the Examples section below.

上述多種方式都可以在Select Into時避掉繼承Identity的屬性，大家可以自行參考。

但由於我的表欄位上百個，上述有幾個方法都是要一併寫出欄位名稱的，這對我來說太過麻煩。我只想Select * 來複製。

因次我們採用Join來處理這一個問題，可使用下面Demo Code。

Create Table tb1(id int identity,cname nvarchar(10));
GO
Insert Into tb1(cname) Values(N'Rock');
GO
--利用Select Into 外加Join 來產生一個暫存表#tb1
--其格式同時體表tb1但避開Identity屬性
Select A.* Into #tb1 From tb1 A
Inner Join tb1 B on 1=0;

--嘗試新增資料
Insert Into #tb1 Select * From tb1 Where id=1;
--檢視#tb1及tb1內容
Select * From tb1;
Select * From #tb1;