問題描述
我在不同的服務(wù)器實(shí)例中有兩個(gè)相同的表.一臺(tái)服務(wù)器用于生產(chǎn)，另一臺(tái)用于測(cè)試.使用 SQL management studio 創(chuàng)建的腳本創(chuàng)建的測(cè)試表(右鍵單擊表 --> 腳本表為 --> 創(chuàng)建).要移動(dòng)測(cè)試數(shù)據(jù)，我使用鏈接服務(wù)器和以下代碼:
set identity_insert ...在插入到<Server>.<DB>.<schema>.<TestTb>從<Server>.<DB>.<schema>.<ProdTB>中選擇前100個(gè)*set identity_insert ...離開 以上適用于我創(chuàng)建的幾個(gè)表.在最后一個(gè)中，我收到列名或提供的值的數(shù)量與創(chuàng)建腳本創(chuàng)建的表中的表定義不匹配"錯(cuò)誤.我檢查了列排序規(guī)則，一切正常.>
我唯一的區(qū)別是我沒有創(chuàng)建在生產(chǎn)環(huán)境中找到的所有索引，但我不認(rèn)為這會(huì)導(dǎo)致錯(cuò)誤.
我正在使用 Sql server 2008.
始終 在 insert 語句中指定列列表，并且在 insert...select 中必須始終指定兩次 - 都在insert 子句和 select 子句.
此外，如果您使用 set identity_insert on 而沒有在 insert 子句中明確指定列列表，SQL Server 將引發(fā)錯(cuò)誤，因此即使您確實(shí)獲得了所有列順序正確，在這種情況下您仍然會(huì)收到錯(cuò)誤.
有關(guān)更多信息，請(qǐng)閱讀 Aaron Bertrand 的 要戒掉的壞習(xí)慣:沒有列列表的 SELECT 或 INSERT，Shnugo 在他的評(píng)論中鏈接到.
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.
這篇關(guān)于列名或編號(hào)...與創(chuàng)建腳本創(chuàng)建的表中的表定義不匹配.將行從一個(gè)表插入到另一個(gè)表時(shí)顯示錯(cuò)誤的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！