SQL Server Error: Column Name or Number of Supplied Values Does Not Match Table Definition

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

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

You need to specify the column name as well, like this:

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

So it will be like this:

insert into OrderPlaced values ('13:40');

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

I always recommend to be explicit about the columns you are inserting into - so change your INSERT statement to be like this:

DECLARE @OutputTbl TABLE ([ID] INT); 

INSERT INTO #TEMP_Master_DimensionValues ([Name], [FullName], ID_DimensionHierarchyType, StartDate, EndDate)
OUTPUT INSERTED.[ID] INTO @OutputTbl([ID])
    SELECT 
        'April01-17' [Name], 
        '''Week of ''' + CONVERT(VARCHAR, (SELECT Min('2021-04-01') FROM Master_DimensionValues), 107) [FullName],
        '3' [ID_DimensionHierarchyType], 
        '2021-04-01' [StartDate], 
        NULL [EndDate]; 

Also, I'd highly recommend not just making everything a varchar(max) - use the most appropriate datatype - always - so a StartDate or EndDate should really be DATE (or alternatively DATETIME2(n)) - but most certainly NOT varchar(max)!

Column name or number of supplied values does not match table definition when trying to upload data to my DB

If you indeed wish to INSERT data then you need to pass real values to all columns that are mark NOT NULL in the table definition--except autogenerated columns like "ID".

So your insert query should look in this:

comm.CommandText = "INSERT INTO Portfolio (Image, Description, ImageData) VALUES(@Image, @Description, @ImageData)";

And the parameters:

comm.Parameters.AddWithValue("@Image", “myImage”);
comm.Parameters.AddWithValue("@Description", “WhateverYouNeed”);
comm.Parameters.AddWithValue("@ImageData", image);

OR FOR UPDATE
If you want to update an existing value you will need an identifier to locate the row and field you wish to UPDATE. In this case, you would need the "ID" of the image you wish to update.

comm.CommandText = “UPDATE [Portfolio] SET [ImageData] = @Image WHERE [ID] = @ID”;

And the parameters:

comm.Parameters.AddWithValue("@Image", “myImage”);
comm.Parameters.AddWithValue(“@ID”, 123); // or whatever the ID

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

This is why you should always declare your columns both in your INSERT and SELECT.

Change both of them to declare all the columns you are inserting and selecting, so your statement looks something like this:

INSERT INTO dbo.unactionedautotraders (code, Ref, contacted, DaySS)
SELECT C.code, P.Ref, C.contacted, P.DaySS
FROM dbo.autotr_2menl P
     LEFT JOIN Customers C ON P.Ref = C.Code
WHERE C.contacted IS NULL
  AND P.DaySS >= 1; --Not sure why this was a varchar?

It's also very strongly advised to never use * in a persisted object. When you use SELECT * in a VIEW SQL Server will "convert" that to the current list of columns in the object at that point, not when the VIEW is run. Therefore if you add more columns to the table, they will not be included. Example:

USE Sandbox;
GO

CREATE TABLE dbo.YourTable (ID int,
                            MyColumn varchar(10));

INSERT INTO dbo.YourTable (ID,
                           MyColumn)
VALUES(1,'Test')
GO

CREATE VIEW dbo.YourView AS

    SELECT *
    FROM dbo.YourTable

GO

SELECT * --Returns both columns
FROM dbo.YourView;
GO

--Add a new column
ALTER TABLE dbo.YourTable ADD MyDate date;
GO
INSERT INTO dbo.YourTable (ID,
                           MyColumn,
                           MyDate)
VALUES(2,'Test',GETDATE());
GO

SELECT * --Returns only ID and MyColumn, not MyDate
FROM dbo.YourView;
GO
--This will error
SELECT ID,
       MyColumn,
       MyDate
FROM dbo.YourView;

This behaviour is not replicated with an SP.

CREATE PROC dbo.YourProc @ID int
AS BEGIN

    SELECT *
    FROM dbo.YourTable
    WHERE ID = @ID
END;
GO

EXEC dbo.YourProc @ID = 2; --Returns 3 columns
GO

ALTER TABLE dbo.YourTable ADD MyInteger int;
GO
INSERT INTO dbo.YourTable (ID,
                           MyColumn,
                           MyDate,
                           MyInteger)
VALUES(3,'Test',GETDATE(),12);
GO
EXEC dbo.YourProc @ID = 3; --Returns 4 columns

This does not change my recommendation. In fact, I stress it more, as if you were to be inserting data into another table (like you are here) altering the either the source or destination tables will break your SP (maybe that's what happened here):

CREATE TABLE dbo.MyTable (ID int,
                          MyColumn varchar(10),
                          MyDate date,
                          MyInteger int);
GO

CREATE PROC dbo.InsertProc @ID int
AS BEGIN

    INSERT INTO dbo.MyTable
    SELECT *
    FROM dbo.YourTable
    WHERE ID = @ID;
END;
GO

EXEC dbo.InsertProc @ID = 3; --works

GO
ALTER TABLE dbo.MyTable ADD MyTime time(0);
GO
EXEC dbo.InsertProc @ID = 1; --fails;
GO
--Undo and change your table instead
ALTER TABLE dbo.MyTable DROP COLUMN MyTime;
GO
ALTER TABLE dbo.YourTable ADD MyDecimal decimal (10,2);
GO

EXEC dbo.InsertProc @ID = 2; --fails;
GO
SELECT * --Just 1 row
FROM dbo.MyTable;

As you can see, however, if we declare our columns properly (both in the INSERT and SELECT) then this problem does not occur:

--Change proc to have column names
ALTER PROC dbo.InsertProc @ID int
AS BEGIN

    INSERT INTO dbo.MyTable (ID,MyColumn)
    SELECT ID, MyColumn
    FROM dbo.YourTable
    WHERE ID = @ID;
END;
GO

EXEC dbo.InsertProc @ID = 2; --works;
GO
--Add a column to MyTable
ALTER TABLE dbo.MyTable ADD MyTime time(0);
GO

EXEC dbo.InsertProc @ID = 1; --still works;
GO

ALTER TABLE dbo.YourTable ADD MyBinary varbinary(10);
GO
EXEC dbo.InsertProc @ID = 4; --still works, inserts no data as no ID 4
GO

SELECT * --3 rows (some have NULLs)
FROM dbo.MyTable; 

GO
--Clean up
DROP PROC dbo.InsertProc;
DROP TABLE dbo.MyTable;
DROP PROC dbo.YourProc;
DROP VIEW dbo.YourView;
DROP TABLE dbo.YourTable;

No DB Fiddle, as unfortunately they don't replicate the true behaviour.

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

I am getting this error from below query.why is error occurred although the number of columns are equal.

They are not equal..

your table..

CREATE TABLE #tmp_statement (
  PurchaseInvoiceID int,
  PurchaseInvoiceNo  varchar(max),
  PurchaseInvoiceDate date
)

Your insert..

 INSERT INTO #tmp_statement
    VALUES (@PurchaseInvoiceID, @PurchaseInvoiceNo, @PurchaseInvoiceDate, @Debit,@Balance)

---

Related Topics