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