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

Question

In the 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 the tables have the same structure, same column names and same data types.

One more possibility is that the DB collation on your TEMP DB and the DB you are inserting into do not match. — Joshua Drake, Oct 25 '13 at 16:41
Isn't this highly flaggable for not posting the schemas of the tables? "I am sure" is not sufficient: we've all been in enough situations where we can't see the wood for the trees to know that our own certainty can be woefully incorrect, and it takes posting a proper volume of information about the problem for someone else to point out the obvious thing we're missing. — underscore_d, Dec 31 '18 at 19:33
You should also have a look at all triggers. You will get the exact same message if you have a badly written trigger inserting into something like an audit table. — DJ van Wyk, Feb 16 '21 at 08:01

score 57 · Accepted Answer · edited May 23 '17 at 12:10

57

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

edited May 23 '17 at 12:10

Community

1
1

answered Jul 20 '09 at 11:10

gbn

422,506
82
585
676

score 31 · Answer 2 · edited Oct 19 '21 at 22:04

31

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 fullname field (computed from first and last, etc)

edited Oct 19 '21 at 22:04

Dharman

30,962
25
85
135

answered Aug 09 '12 at 01:33

Kris Benedict

319
3
2

Worked for me. My problem wasn't inserting without explicitly stating the column names, but when I tried to join two tables that weren't created as such. Once I added the column names, my join worked. Must have been that computed column that was monkeying things up! – midoriha_senpai Nov 07 '18 at 19:54
Thank you for the computed column issue I can confirm this is true. I had a table with the exact same number of columns in the same order. The only difference is that the source table uses a computed column. – David Hirst Jan 07 '20 at 14:31
I think I've just hit this using SELECT INTO and adding two ROW_NUMBER() columns (partitioned differently). Its coming from a table value function too :/ – Hecatonchires Sep 07 '20 at 02:41

score 23 · Answer 3 · answered Jul 20 '09 at 11:25

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

score 13 · Answer 4 · edited Oct 19 '21 at 22:05

13

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')

edited Oct 19 '21 at 22:05

Dharman

30,962
25
85
135

answered Mar 26 '16 at 03:42

CodeCoder

141
1
4

score 6 · Answer 5 · answered Dec 17 '18 at 16:59

6

Beware of triggers. Maybe the issue is with some operation in the trigger for inserted rows.

answered Dec 17 '18 at 16:59

linitux

341
4
5

Thank you for this! This error appeared a few hours after a production deployment on features we hadn't even touched. So, I asked the database admin about any new triggers... – Lewis Hazell Feb 06 '23 at 20:48

score 2 · Answer 6 · answered Mar 06 '12 at 14:49

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

score 1 · Answer 7 · answered Jan 09 '14 at 11:33

1

The computed columns make the problem. Do not use SELECT *. You must specify each fields after SELECT except computed fields

answered Jan 09 '14 at 11:33

GyurcIT

19
2

score 1 · Answer 8 · answered Mar 25 '21 at 20:07

1

some sources for this issues are as below

1- Identity column ,

2- Calculated Column

3- different structure

so check those 3 , i found my issue was the second one ,

answered Mar 25 '21 at 20:07

Ali

1,080
16
22

score 0 · Answer 9 · edited Oct 19 '21 at 22:05

I had the same problem, and the way I worked around it is probably not the best but it is working now.

It involves creating a linked server and using dynamic sql - not the best, but if anyone can suggest something better, please comment/answer.

declare @sql nvarchar(max)


DECLARE @DB_SPACE TABLE (
[DatabaseName] NVARCHAR(128) NOT NULL,
[FILEID] [smallint] NOT NULL,
[FILE_SIZE_MB] INT NOT NULL DEFAULT (0),
[SPACE_USED_MB] INT NULL DEFAULT (0),
[FREE_SPACE_MB] INT NULL DEFAULT (0),
[LOGICALNAME] SYSNAME NOT NULL,
[DRIVE] NCHAR(1) NOT NULL,
[FILENAME] NVARCHAR(260) NOT NULL,
[FILE_TYPE] NVARCHAR(260) NOT NULL,
[THE_AUTOGROWTH_IN_KB] INT NOT NULL DEFAULT(0)
,filegroup VARCHAR(128)
,maxsize VARCHAR(25)

PRIMARY KEY CLUSTERED ([DatabaseName] ,[FILEID] )
)  


SELECT @SQL ='SELECT [DatabaseName],
        [FILEID],
        [FILE_SIZE_MB],
        [SPACE_USED_MB],
        [FREE_SPACE_MB],
        [LOGICALNAME],
        [DRIVE],
        [FILENAME],
        [FILE_TYPE],
        [THE_AUTOGROWTH_IN_KB]
        ,filegroup
        ,maxsize FROM OPENQUERY('+ QUOTENAME('THE_MONITOR') + ','''+ ' EXEC MASTER.DBO.monitoring_database_details '  +''')'
exec sp_executesql @sql


      INSERT INTO @DB_SPACE(
                            [DatabaseName],
                            [FILEID],
                            [FILE_SIZE_MB],
                            [SPACE_USED_MB],
                            [FREE_SPACE_MB],
                            [LOGICALNAME],
                            [DRIVE],
                            [FILENAME],
                            [FILE_TYPE],
                            THE_AUTOGROWTH_IN_KB,
                            [filegroup],
                            maxsize
                          )

      EXEC SP_EXECUTESQL @SQL

This is working for me now. I can guarantee the number of columns and type of columns returned by the stored procedure are the same as in this table, simply because I return the same table from the stored procedure.

score 0 · Answer 10 · answered Sep 14 '16 at 04:48

For me the culprit is int value assigned to salary

Insert into Employees(ID,FirstName,LastName,Gender,Salary) values(3,'Canada', 'pa', 'm',15,000)

in salary column When we assign 15,000 the compiler understand 15 and 000.

This correction works fine for me. Insert into Employees(ID,FirstName,LastName,Gender,Salary) values(4,'US', 'sam', 'm',15000)

score 0 · Answer 11 · edited Dec 10 '19 at 20:33

0

Check your id. Is it Identity? If it is then make sure it is declared as ID not null Identity(1,1)

And before creating your table , Drop table and then create table.

edited Dec 10 '19 at 20:33

mypetlion

2,415
5
18
22

answered Jun 07 '17 at 11:05

Muñir Ur Rêhmåñ

1
1

score 0 · Answer 12 · answered Oct 01 '18 at 13:17

Update to SQL server 2016/2017/…
We have some stored procedures in place to import and export databases.
In the sp we use (amongst other things) RESTORE FILELISTONLY FROM DISK where we create a table "#restoretemp" for the restore from file.

With SQL server 2016, MS has added a field SnapshotURL nvarchar(360) (restore url Azure) what has caused the error message.
After I have enhanced the additional field, the restore has worked again.
Code snipped (see last field):

 SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BackupFile , '''')
CREATE TABLE #restoretemp
(
LogicalName nvarchar(128)
,PhysicalName nvarchar(128)
,[Type] char(1)
,FileGroupName nvarchar(128)
,[Size] numeric(20,0)
,[MaxSize] numeric(20,0)
,FileID bigint
,CreateLSN numeric(25,0)
,DropLSN numeric(25,0) NULL
,UniqueID uniqueidentifier
,ReadOnlyLSN numeric(25,0)
,ReadWriteLSN numeric(25,0)
,BackupSizeInByte bigint
,SourceBlockSize int
,FilegroupID int
,LogGroupGUID uniqueidentifier NULL
,DifferentialBaseLSN numeric(25,0)
,DifferentialbaseGUID uniqueidentifier
,IsReadOnly bit
,IsPresent bit
,TDEThumbprint varbinary(32)
-- Added field 01.10.2018 needed from SQL Server 2016 (Azure URL)
,SnapshotURL nvarchar(360)
)

INSERT #restoretemp EXEC (@query)
SET @errorstat = @@ERROR
if @errorstat <> 0 
Begin
if @Rueckgabe = 0 SET @Rueckgabe = 6
End
Print @Rueckgabe

I know this answer wasn't what the original person asked for but it did help me out. So thanks. — Will Young, Mar 11 '19 at 17:15

score 0 · Answer 13 · edited Nov 17 '20 at 11:28

0

The problem I had that caused this error was that I was trying to insert null values into a NOT NULL column.

edited Nov 17 '20 at 11:28

Dharman

30,962
25
85
135

answered Nov 17 '20 at 11:22

user489998

4,473
2
29
35

score 0 · Answer 14 · answered Feb 21 '22 at 22:16

0

In my case, I had:

insert into table1 one
select * from same_schema_as_table1 same_schema
left join...

and I had to change select * to select same_schema.*.

answered Feb 21 '22 at 22:16

eyelesscactus54

86
1
4

score 0 · Answer 15 · edited Apr 04 '22 at 07:35

0

You're missing column name after TableName in insert query:

INSERT INTO TableName**(Col_1,Col_2,Col_3)** VALUES(val_1,val_2,val_3)

edited Apr 04 '22 at 07:35

Dale K

25,246
15
42
71

answered Mar 15 '22 at 02:25

O Thạnh Ldt

1,103
10
11

score 0 · Answer 16 · answered Nov 15 '22 at 08:03

0

In my case the problem was that the SP I was executing returned two result sets, and only the second result set was matching the table definition.

answered Nov 15 '22 at 08:03

ashilon

1,791
3
24
41

score 0 · Answer 17 · edited Mar 03 '23 at 09:58

0

Whilst the other answers work fine for insert purposes, you can also just run the following:

IF OBJECT_ID('tempdb..@table) IS NOT NULL
    DROP TABLE @table. 
Then go back and rerun 

DECLARE @Table TABLE(        
 Val1 VARCHAR(MAX),
 Val2 VARCHAR(MAX) )  
INSERT INTO @Table SELECT '1' .

It should work.

edited Mar 03 '23 at 09:58

Eric Aya

69,473
35
181
253

answered Mar 03 '23 at 09:47

Ayo

1
1

Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, **can you [edit] your answer to include an explanation of what you're doing** and why you believe it is the best approach? – Jeremy Caney Mar 08 '23 at 00:18

score 0 · Answer 18 · answered Apr 11 '23 at 23:58

I just solved the same problem after spending quite some time digging up the root cause. The number of input parameters in C# code matched the number of input parameters in the stored procedure. The number of output parameters of the stored procedure also matched the number of variables in the receiving class of the C# code. Every output column name of the stored procedure also matched exactly one variable name of the receiving class.

Finally, after consulting my boss, the problem was solved after I executed the alter procedure command to the stored procedure after doing absolutely nothing. She encountered the same problem before and also did this to solve it.

Pretty weird SQL Server related stuff!

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

18 Answers18

Linked

Related