SQL server - Msg 213 - Insert Error: Column name or number of supplied values does not match table definition.

Question

In SQL server, I am trying to insert values from one table to another by using the below query :

delete from tblTable1

insert into tblTable1 select * from tblTable1_Link

I am getting the following error :

Column name or number of supplied values does not match table definition.

I am sure that both table having same structure, column names and same data type.

Thank you for your help. I have tried posting this to sqlsvrtip but I got no response so I though I would try here are there seems to be way more activity.

Answer 1

Is one of the columns an IDENTITY column?

SET IDENTITY_INSERT tblTable1 ON
insert into tblTable1 select * from tblTable1_Link
SET IDENTITY_INSERT tblTable1 OFF

Answer 2

Can you please run this statement in your database and give us the output??

SELECT 
    c.name,
    c.is_identity,
    c.is_computed, 
    t.name 'Type name'
FROM sys.columns c
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE object_id = object_ID('tblTable1')

Questions:

• is there any column that has is_identity set to 1 (true) ?
• is there any column that has is_computed set to 1 (true) ?
• is there any column with a type of timestamp or rowversion ??

If you have any of those columns: you cannot easily set any of these in an INSERT statement. You can set the identity columns with some extra work, but computed columns or columns of type TIMESTAMP / ROWVERSION cannot be set under any circumstances.

That's why I'd recommend to always explicitly specify the list of columns - even if you need all of them:

INSERT INTO dbo.tblTable1(col1, col2, ...., colX)
   SELECT col1, col2, ...., colX FROM dbo.tblTable1_Link

With this approach, you can leave out any columns that cannot be inserted quite easily...

Answer 3

+1 for @Fosco as that's most likely the answer.

One note though, if you're simply caching a linked table and thus emptying it each time then you could just do something like:

DROP TABLE tblTable1 -- assuming there are no FKs

select * INTO tblTable1 from tblTable1_Link
--  Then Re-create keys / indexes

This will mean that if the schema of tblTable1_Link changes then that change will also be in the destination table.