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 :
我在不同的服务器实例中有两个相同的表。一台服务器是生产服务器,另一台服务器用于测试。使用SQL管理工作室创建的脚本创建的测试表(右键单击表 - >脚本表为 - >创建)。要移动测试数据,我使用链接服务器和以下代码:
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.
上面的内容适用于我创建的几个表。在最后一个,我得到“列名称或提供的值的数量与创建脚本创建的表中的表定义不匹配”error.i已检查列整理,一切正常。
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.
我正在使用Sql server 2008。
1 个解决方案
#1
1
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.
始终在insert语句中指定列列表,并在insert ...中选择必须始终指定它两次 - 在insert子句和select子句中。
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.
此外,如果在未明确指定insert子句中的列列表的情况下使用set identity_insert,SQL Server将引发错误,因此即使您确实以正确的顺序获取了所有列,在这种情况下仍会出现错误。
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.
有关更多信息,请阅读Aaron Bertrand的糟糕习惯:SELECT或INSERT没有Shnugo在其评论中链接的列列表。
#1
1
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.
始终在insert语句中指定列列表,并在insert ...中选择必须始终指定它两次 - 在insert子句和select子句中。
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.
此外,如果在未明确指定insert子句中的列列表的情况下使用set identity_insert,SQL Server将引发错误,因此即使您确实以正确的顺序获取了所有列,在这种情况下仍会出现错误。
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.
有关更多信息,请阅读Aaron Bertrand的糟糕习惯:SELECT或INSERT没有Shnugo在其评论中链接的列列表。