SET IDENTITY_INSERT [db1].[dbo].Subscriber ON
INSERT INTO [db1].[dbo].Subscriber
SELECT * FROM [db2].[dbo].SubScriber
PRINT 'Successfully Re-imported data from SubScriber backup'
SET IDENTITY_INSERT [db1].[dbo].Subscriber OFF
我想要的只是一種從一個表中復制資料的動態方式，該表與另一個資料庫中的另一個表具有相同的設定，但我繼續收到此錯誤：
表 'db1.dbo.MsSubProject' 中標識列的顯式值只能在使用列串列并且 IDENTITY_INSERT 為 ON 時指定。
我已經明確地將 IDENTITY_INSERT 設定為 ON。另外，即使我嘗試像這樣明確寫出表格列：
SET IDENTITY_INSERT [db1].[dbo].Subscriber ON
INSERT INTO [db1].[dbo].Subscriber (SubscriberGUID, ItemGUID_Entity, SubscriberID, SubscriberRegionID, SubscriberTypeID, ID, SubscriberNameFull, SubscriberEmail, SubscriberLogin, SubscriberPassword, Active, DateCreated, DateDeleted)
SELECT * FROM [db2].[dbo].SubScriber
PRINT 'Successfully Re-imported data from SubScriber backup'
SET IDENTITY_INSERT [db1].[dbo].MsSubscriber OFF
這也行不通...
我究竟做錯了什么？我在 StackOverflow 上找到的針對此特定問題的每個解決方案都不適用于我。
uj5u.com熱心網友回復：
您還應該養成在陳述句中使用顯式列串列的習慣SELECT！SELECT *在 SSMS 中查詢 ad-hoc 樣式是可以的 - 但它應該被禁止在任何生產代碼中使用！
嘗試這個：
SET IDENTITY_INSERT [db1].[dbo].Subscriber ON
INSERT INTO [db1].[dbo].Subscriber (SubscriberGUID, ItemGUID_Entity, SubscriberID, SubscriberRegionID, SubscriberTypeID, ID, SubscriberNameFull, SubscriberEmail, SubscriberLogin, SubscriberPassword, Active, DateCreated, DateDeleted)
SELECT
SubscriberGUID, ItemGUID_Entity, SubscriberID, SubscriberRegionID,
SubscriberTypeID, ID, SubscriberNameFull, SubscriberEmail,
SubscriberLogin, SubscriberPassword, Active, DateCreated, DateDeleted
FROM [db2].[dbo].SubScriber
PRINT 'Successfully Re-imported data from SubScriber backup'
SET IDENTITY_INSERT [db1].[dbo].MsSubscriber OFF
uj5u.com熱心網友回復：
如果你真的不想寫列串列，你可以使用動態 SQL
DECLARE @cols1 nvarchar(max), @cols2 nvarchar(max);
SELECT
@cols1 = STRING_AGG(QUOTENAME(c1.name, ',')) WITHIN GROUP (ORDER BY c1.name),
@cols2 = STRING_AGG(QUOTENAME(c2.name, ',')) WITHIN GROUP (ORDER BY c1.name) -- same order
FROM db1.sys.columns c1
JOIN db2.sys.columns c2
WHERE c1.object_id = OBJECT_ID(N'[db1].[dbo].Subscriber')
AND c2.object_id = OBJECT_ID(N'[db2].[dbo].Subscriber');
DECLARE @sql nvarchar(max) = N'
SET IDENTITY_INSERT [db1].[dbo].Subscriber ON;
INSERT INTO [db1].[dbo].Subscriber
(' @cols1 N')
SELECT
' @cols2 N'
FROM [db2].[dbo].SubScriber;
PRINT ''Successfully Re-imported data from SubScriber backup'';
SET IDENTITY_INSERT [db1].[dbo].Subscriber OFF;
';
EXEC sp_executesql @sql;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/358333.html
標籤：sql sql-server 存储过程
上一篇：如何從SQLServer存盤程序的3列中獲取MAX值？
下一篇：按過去5年過濾的SQL陳述句