column name or number … not match table definition

问题:

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.

回答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.

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.