Ayman Ayman - 3 months ago 12
SQL Question

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.

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


Line 46 is
INSERT INTO #tmp_statement


Query

DECLARE @PurchaseInvoiceID int,
@PurchaseInvoiceNo varchar(max),
@PurchaseInvoiceDate date,
@Debit numeric(9, 2),
@Balance numeric(9, 2)
SET @Balance = 0;
CREATE TABLE #tmp_statement (
PurchaseInvoiceID int,
PurchaseInvoiceNo varchar(max),
PurchaseInvoiceDate date
)
DECLARE rt_cursor CURSOR FOR
SELECT
dbo.PurchaseInvoices.PurchaseInvoiceID,
dbo.PurchaseInvoices.PurchaseInvoiceNo,
dbo.PurchaseInvoices.PurchaseInvoiceDate,
CONVERT(decimal(9, 2), SUM(RequisitionDetails.ReceivedQuantity * RequisitionDetails.UnitPrice)) AS Debit
FROM dbo.PurchaseOrders
INNER JOIN dbo.Requisitions
ON dbo.PurchaseOrders.RequisitionID = dbo.Requisitions.RequisitionID
INNER JOIN dbo.Restaurants
ON dbo.Requisitions.RestaurantID = dbo.Restaurants.RestaurantID
INNER JOIN dbo.Suppliers
ON dbo.PurchaseOrders.SupplierID = dbo.Suppliers.SupplierID
INNER JOIN dbo.Categories
ON dbo.Requisitions.CategoryID = dbo.Categories.CategoryID
INNER JOIN dbo.PurchaseInvoices
ON dbo.PurchaseOrders.PurchaseOrderID = dbo.PurchaseInvoices.PurchaseInvoiceID
INNER JOIN dbo.RequisitionDetails
ON dbo.RequisitionDetails.RequisitionID = dbo.Requisitions.RequisitionID
INNER JOIN dbo.AccountingDocuments
ON dbo.PurchaseInvoices.DocumentID = dbo.AccountingDocuments.DocumentID
INNER JOIN dbo.Statement
ON dbo.PurchaseInvoices.PurchaseInvoiceID = dbo.Statement.PurchaseInvoiceID
WHERE Suppliers.SupplierID = 1
AND dbo.PurchaseInvoices.PurchaseInvoiceDate BETWEEN '2016-08-1' AND '2016-08-31'
GROUP BY dbo.PurchaseOrders.PurchaseOrderID,
dbo.PurchaseInvoices.PurchaseInvoiceDate,
dbo.PurchaseInvoices.PurchaseInvoiceNo,
dbo.PurchaseInvoices.PurchaseInvoiceID
OPEN rt_cursor
FETCH NEXT FROM rt_cursor INTO @PurchaseInvoiceID, @PurchaseInvoiceNo, @PurchaseInvoiceDate, @Debit
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Balance = @Balance + @Debit
INSERT INTO #tmp_statement
VALUES (@PurchaseInvoiceID, @PurchaseInvoiceNo, @PurchaseInvoiceDate, @Debit,@Balance)
FETCH NEXT FROM rt_cursor INTO @PurchaseInvoiceID, @PurchaseInvoiceNo, @PurchaseInvoiceDate, @Debit
END
CLOSE rt_cursor
DEALLOCATE rt_cursor
SELECT
*
FROM #tmp_statement

Answer

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