CODEWITHSUNDEEP
sql-server
NickJH
NickJH

Reputation: 81

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

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.

Upvotes: 5

Views: 20455

Answers (3)

Joel Mansford
Joel Mansford

Reputation: 1316

+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.

Upvotes: 0

marc_s
marc_s

Reputation: 755381

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

Upvotes: 1

Fosco
Fosco

Reputation: 38526

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

Upvotes: 3

Related Questions