Ошибка column name or number of supplied values does not match table definition

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.

Источник

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.

Источник

You’re not showing us what you’re doing — but based on the table structure, this is what you should do:

  • create an INSERT statement that explicitly lists the columns it will insert into — assuming that ID might be an IDENTITY column that you don’t want / can’t insert into

  • define the exact number of values to fill into these columns

So your INSERT statement should be something like:

INSERT INTO dbo.tbl_Post (cat_id, ngo_id, title, description, active)
VALUES (42, 4711, 'Some title', 'Some description', 1)

What you should definitely get in the habits of avoiding is using INSERT INTO dbo.tblPost without explicitly defining the list of column to insert into. This is just a recipe for disaster, as soon as you change your table definition, all your existing INSERTs will break since they don’t match the table definition anymore.

Therefore: always explicitly define the list of columns that an INSERT statement should fill data into!

Also see Aaron Bertrand’s excellent blog post on the topic:
Bad habits to kick: using SELECT * / omit the column list

Источник

Error message 213 is a common error that happens when you try to insert values into a table without explicitly specifying the column names.

The error looks like this:

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

It occurs when you specify the wrong number of values for that table. In other words, the number of values you provide doesn’t match the number of columns in the table.

Example

Here’s an example to demonstrate.

INSERT INTO Customers
VALUES ('Jake');

Result:

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

In my case, the problem is that the table actually contains three columns. My table definition looks like this:

CREATE TABLE Customers (
CustomerId int IDENTITY(1,1) NOT NULL,
FirstName nvarchar(255),
LastName nvarchar(255)
);

I’m trying to insert a value, but SQL Server doesn’t know which column it should go into, hence the error.

I would also get the same error if I tried to insert too many values. For example, the following also produces the same error.

INSERT INTO Customers
VALUES ('Jake', 'Smith', 'New York', 'USA');

Result:

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

How to Fix the Error

One way to fix this, is to ensure that the number of values you try to insert actually matches the number of columns in the table.

A better way to do it is explicitly specify the column names in your INSERT statement. Doing this will ensure you don’t accidentally insert data into the wrong columns.

So depending on which values I want to insert, I could rewrite my example to this:

INSERT INTO Customers (FirstName)
VALUES ('Jake');

Or this:

INSERT INTO Customers (FirstName, LastName)
VALUES ('Jake', 'Smith');

Implicit Column Names

As mentioned, it’s better to explicitly spell out each column name in your INSERT statement (as I did in the previous example).

I could however, change my example to use implicit column names, like this:

INSERT INTO Customers
VALUES (1, 'Jake', 'Smith');

However, this could now cause a separate issue regarding the identity column. See How to Insert an Explicit Value into an Identity Column if you need to do this.

Источник
  • Remove From My Forums

  • Question

  • Hello team,

    I have written this code:

    IF OBJECT_ID(‘Employees’) IS NOT NULL drop table  Employees
    GO

     
    CREATE TABLE Employees
    (
    EmployeeID Char(10) NOT NULL Primary Key
    ,FirstName nVarChar(30) NOT NUll
    ,LastName nVarChar(130) NOT NULL
    ,DateOfBirth Date
    ,DateOfBirthAsText nVarChar(12)
    ,Age int
    ,Gender Char(1)
    ,Zip Int
    ,PolicyHolderMemberID Char(13) NOT NULL
    ,SupervisorName nVarChar(150)
    )

    Declare @EmployeeTest Table
    (
     EmployeeID Char(10)
    ,FullName nVarChar(150) NOT NULL
    ,HireDate Date
    ,Zip Int
    ,SupervisorName nVarChar(150)
    )

    INSERT INTO @EmployeeTest
      (EmployeeID, FullName, HireDate, Zip, SupervisorName)
    VALUES  (’01-1′,’Deepita Candy’, ‘1998-01-01’, 92677, ‘Meryem’);

    INSERT INTO @EmployeeTest
      (EmployeeID, FullName, HireDate, Zip, SupervisorName)
    VALUES  (’01-2′,’Marcella Nick’, ‘1999-01-12′, 99011,’Kumru’);

    INSERT INTO @EmployeeTest
      (EmployeeID, FullName, HireDate, Zip, SupervisorName)
    VALUES  (’01-3′,’Dila Jebeji’, ‘1988-01-01′, 95454,’Kartal’);

    INSERT INTO @EmployeeTest
      (EmployeeID, FullName, HireDate, Zip, SupervisorName)
    VALUES  (’01-4′,’Barish Arduc’, ‘1987-09-01′, 12345,’Raven’);

    INSERT INTO @EmployeeTest
      (EmployeeID, FullName, HireDate, Zip, SupervisorName)
    VALUES  (’01-5′,’Gelsyn Circassian’, ‘1963-12-01′, 56789,’Burcu’);

    —SELECT * From @EmployeeTest

    —Populate Employees Table

    INSERT into Employees —-Line 222
    SELECT EmployeeID
    ,dbo.udf_GetFirstName(FullName) AS FirstName
    ,dbo.udf_GetLastName(FullName)  AS LastName
    ,DateADD(Year, -21, HireDate)  AS DateOfBirth
    ,Convert(nVarChar(12), DateADD(Year,-21, HireDate), 101) AS DateOfBirthAsText
    ,DateDiff(Y,DATEADD(Year, -21, HireDate),GetDate()) AS AGE
    ,(Case
     WHEN Left(Zip,1) in (1, 3, 5, 7, 9) THEN ‘M’
     ELSE ‘F’
     End) AS Gender
     ,EmployeeID AS PolicyHolderMemberID
     ,SupervisorName
    FROM @EmployeeTest

    SELECT * From Employees

    ————————————

    The code on the top gives me an error message:

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

    What is the problem? 

    Respectfully,

    CloudsInSky

    CloudsInSky

Answers

  • Hi cloudsInSky, 

    This error occurs when doing an INSERT where the columns list is not specified and the values being inserted, either through the VALUES clause or through a SELECT subquery, are either more than or less than the columns in the table.  

    Please check . 

    IF OBJECT_ID('Employees') IS NOT NULL drop table  Employees
GO
 
CREATE TABLE Employees
(
EmployeeID Char(10) NOT NULL Primary Key
,FirstName nVarChar(30) NOT NUll
,LastName nVarChar(130) NOT NULL
,DateOfBirth Date
,DateOfBirthAsText nVarChar(12)
,Age int
,Gender Char(1)
-----,Zip Int
,PolicyHolderMemberID Char(13) NOT NULL
,SupervisorName nVarChar(150)
)

INSERT into Employees ----Line 222
SELECT EmployeeID
,dbo.udf_GetFirstName(FullName) AS FirstName
,dbo.udf_GetLastName(FullName)  AS LastName
,DateADD(Year, -21, HireDate)  AS DateOfBirth
,Convert(nVarChar(12), DateADD(Year,-21, HireDate), 101) AS DateOfBirthAsText
,DateDiff(Y,DATEADD(Year, -21, HireDate),GetDate()) AS AGE
,(Case
 WHEN Left(Zip,1) in (1, 3, 5, 7, 9) THEN 'M'
 ELSE 'F'
 End) AS Gender
 ,EmployeeID AS PolicyHolderMemberID
 ,SupervisorName
FROM @EmployeeTest
SELECT * From Employees

    Best Regards,

    Rachel 

    MSDN Community Support
    Please remember to click «Mark as Answer» the responses that resolved your issue, and to click «Unmark as Answer» if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to
    MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by

      Tuesday, May 12, 2020 9:18 AM

    • Marked as answer by
      cloudsInSky
      Thursday, May 14, 2020 2:20 AM

Источник

  • Ошибка collision prevention assist plus не действует mercedes
  • Ошибка collecting data for crash dump initializing disk for crash dump
  • Ошибка collar 43 good wolf
  • Ошибка codex64 dll как исправить
  • Ошибка codex 64 dll