MSSQL Set IDENTITY_INSERT 設置為 ON 使用Insert into 的所有欄位

mssql insert into identity_insert

cake955214 2022-11-03 16:36:55 ‧ 5194 瀏覽

分享至

我在從 Atable 移資料給 Btable 時遇到一個問題
如果 Atable 有設置(識別規格自動+1) 移動到 Btable 的話
必須 set IDENTITY_INSERT 要設置為 ON

可以跑的程式碼：

set identity_insert dbo.b ON;
INSERT INTO dbo.b(id,name) SELECT * FROM dbo.a;
set identity_insert dbo.b OFF;

我想要實現：

set identity_insert dbo.b ON;
INSERT INTO dbo.b(想在這裡取得所有欄位 但是填 * 會失敗) SELECT * FROM dbo.a;
set identity_insert dbo.b OFF;

因為做的是一支自動化程式所以希望填 * (全部)

網上查到 https://jonesyeh.wordpress.com/2014/01/20/set-identity_insert-tablename-on%E6%B3%A8%E6%84%8F%E4%BA%8B%E9%A0%85/
但是不知道有沒有不用手填欄位的方法

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

純真的人

iT邦大師 1 級 ‧ 2022-11-03 16:59:56

如果你欄位順序位置、數量正確~以下是可以的~

set identity_insert dbo.b ON;
INSERT INTO dbo.b SELECT * FROM dbo.a;
set identity_insert dbo.b OFF;

回應 9
分享
檢舉

看更多先前的回應...收起先前的回應...

cake955214 iT邦新手 5 級 ‧ 2022-11-03 17:06:04 檢舉

我原本也是這樣寫，但是編譯時會出現
位於資料表 'dbo.b' 的識別欄位其外顯值只有當使用了資料行清單且 IDENTITY_INSERT 為 ON 時才能指定。

cake955214 iT邦新手 5 級 ‧ 2022-11-03 17:08:02 檢舉

我的資料表 b是a複製出來的，把 b 清空後再把 a 的值倒入給b，所以欄位應該是一致的

實習工程師 iT邦新手 1 級 ‧ 2022-11-03 17:19:49 檢舉

是不是 B資料表內的流水號所導致的錯誤?

cake955214 iT邦新手 5 級 ‧ 2022-11-03 17:34:13 檢舉

如果沒有流水號那語法就能過
只是我是做一個資料表的轉移
難保對方的資料表沒有流水號

純真的人 iT邦大師 1 級 ‧ 2022-11-03 17:34:17 檢舉

那清除B資料表~在新增全部吧~

truncate table dbo.b
set identity_insert dbo.b ON;
INSERT INTO dbo.b SELECT * FROM dbo.a;
set identity_insert dbo.b OFF;

純真的人 iT邦大師 1 級 ‧ 2022-11-03 17:36:28 檢舉

如果B資料表不能清除@@~那你就要手動增加欄位，並去取B資料表自動加1

cake955214 iT邦新手 5 級 ‧ 2022-11-03 17:59:30 檢舉

可以清除不過dbo.b後面不寫欄位就會報錯說沒使用
set identity_insert dbo.b ON;

cake955214 iT邦新手 5 級 ‧ 2022-11-03 18:00:10 檢舉

果然還是要老老實實去撈資料表欄位回來再填入這裡嗎
INSERT INTO dbo.b(這裡) SELECT * FROM dbo.a;

純真的人 iT邦大師 1 級 ‧ 2022-11-03 18:07:00 檢舉

那就刪除~identity_insert反正用不到

truncate table dbo.b;
INSERT INTO dbo.b SELECT * FROM dbo.a;

登入發表回應

實習工程師

iT邦新手 1 級 ‧ 2022-11-03 17:30:58

可以將 dbo.b 的流水號關閉
再下一次下面的語法就可以了。
INSERT INTO dbo.b SELECT * FROM dbo.a

回應
分享
檢舉

登入發表回應

OuJiaHao

iT邦新手 4 級 ‧ 2022-11-04 12:03:36

如果透過 dynamic SQL 的方式呢?
思路是透過 information_schema 取出 table 的各個欄位
接下來再做拼接，來組成一個有效的SQL
最後再進行執行這樣

declare @Columns nvarchar(max)
SELECT @Columns = String_Agg(COLUMN_NAME, ',')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'dbo.b'

declare @cmd nvarchar(max) = 
'set identity_insert dbo.b ON;
INSERT INTO dbo.b('
+ @Columns +
') SELECT * FROM dbo.a;
set identity_insert dbo.b OFF;'

exec(@cmd)

然後如果 a 跟 b 兩張 Table 的 schema 相同的話
參考實習工程師大大的做法即可