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

Question

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 the tables have same structure  same column names and same data types   Please help

User · Answer

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

User · Answer

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    hope this helps

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

This is an older post but 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

User · Answer

I hope you have found a good solution  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   thanks and regards marcelo

User · Answer

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  lt  gt  0  Begin if  Rueckgabe   0 SET  Rueckgabe   6 End Print  Rueckgabe

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql