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