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

Column name or number of supplied values does not match table definition: They don’t have the same structure… I can guarantee they are different

I know you’ve already created it… There is already an object named ‘tbltable1’ in the database

What you may want is this (which also fixes your other issue):

Drop table tblTable1

select * into tblTable1 from tblTable1_Link

Second Solution :

I want to also mention that if you have something like

insert into blah
       select * from blah2

and blah and blah2 are identical keep in mind that a computed column will throw this same error…

I just realized that when the above failed and I tried

insert into blah (cola, colb, colc)
       select cola, colb, colc from blah2

In my example, it was full name field (computed from first and last, etc)


Third Solution is –

for inserts it is always better to specify the column names see the following

DECLARE @Table TABLE(
        Val1 VARCHAR(MAX)
)

INSERT INTO @Table SELECT '1'

works fine, changing the table def to causes the error

DECLARE @Table TABLE(
        Val1 VARCHAR(MAX),
        Val2 VARCHAR(MAX)
)

INSERT INTO @Table SELECT '1'

Msg 213, Level 16, State 1, Line 6 Insert Error: Column name or number of supplied values does not match table definition.

But changing the above to

DECLARE @Table TABLE(
        Val1 VARCHAR(MAX),
        Val2 VARCHAR(MAX)
)

INSERT INTO @Table (Val1)  SELECT '1'

works. You need to be more specific with the columns specified

supply the structures and we can have a look


The problem is that you are trying to insert data into the database without using columns. SQL server gives you that error message.

Error: insert into users values('1', '2','3') – this works fine as long you only have 3 columns

If you have 4 columns but only want to insert into 3 of them

Correct: insert into users (firstName,lastName,city) values ('Tom', 'Jones', 'Miami')


Dropping the table was not an option for me, since I’m keeping a running log. If every time I needed to insert I had to drop, the table would be meaningless.

My error was because I had a couple columns in the create table statement that were products of other columns, changing these fixed my problem. eg

create table foo (
field1 as int
,field2 as int
,field12 as field1 + field2 )

create table copyOfFoo (
field1 as int
,field2 as int
,field12 as field1 + field2)  --this is the problem, should just be 'as int'

insert into copyOfFoo
SELECT * FROM foo

Leave a Comment