問題描述
我在不同的服務器實例中有兩個相同的表.一臺服務器用于生產，另一臺用于測試.使用 SQL management studio 創建的腳本創建的測試表(右鍵單擊表 --> 腳本表為 --> 創建).要移動測試數據，我使用鏈接服務器和以下代碼:
set identity_insert ...在插入到<Server>.<DB>.<schema>.<TestTb>從<Server>.<DB>.<schema>.<ProdTB>中選擇前100個*set identity_insert ...離開 以上適用于我創建的幾個表.在最后一個中，我收到列名或提供的值的數量與創建腳本創建的表中的表定義不匹配"錯誤.我檢查了列排序規則，一切正常.>
我唯一的區別是我沒有創建在生產環境中找到的所有索引，但我不認為這會導致錯誤.
我正在使用 Sql server 2008.
始終 在 insert 語句中指定列列表，并且在 insert...select 中必須始終指定兩次 - 都在insert 子句和 select 子句.
此外，如果您使用 set identity_insert on 而沒有在 insert 子句中明確指定列列表，SQL Server 將引發錯誤，因此即使您確實獲得了所有列順序正確，在這種情況下您仍然會收到錯誤.
有關更多信息，請閱讀 Aaron Bertrand 的 要戒掉的壞習慣:沒有列列表的 SELECT 或 INSERT，Shnugo 在他的評論中鏈接到.
I have two identical tables in different server instances. One server is production and the other one is for testing. The testing tables where created by using scripts created by SQL management studio (Right click on table -->script table as --> Create). To move test data i am using a linked server and the following code :
set identity_insert <Server>.<DB>.<schema>.<SomeID> ON
insert into <Server>.<DB>.<schema>.<TestTb>
select top 100 * from <Server>.<DB>.<schema>.<ProdTB>
set identity_insert <Server>.<DB>.<schema>.<SomeID> OFF
The above worked for a couple of the tables i created. In the last one, i get the "column name or number of supplied values does not match table definition in table created by create script" error.i have checked the Columns collation and everything is ok.
The only difference i have is that i haven't created all the indexes found in the Production env, but i don't really think this causes the error.
I' m working on Sql server 2008.
Always specify the columns list in insert statements, and in insert...select you must always specify it twice - both in the insert clause and in the select clause.
Also, SQL Server will raise an error if you use set identity_insert on without explicitly specifying the columns list in the insert clause, so even if you did get all the columns in the correct order, you would still get an error in this case.
For more information, read Aaron Bertrand's Bad habits to kick: SELECT or INSERT without a column list which Shnugo linked to in his comment.
這篇關于列名或編號...與創建腳本創建的表中的表定義不匹配.將行從一個表插入到另一個表時顯示錯誤的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！