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

Question

I am receiving the following error when create the stored procedure:

Msg 213, Level 16, State 1, Procedure autotramenlunallocated, Line 5 [Batch Start Line 9]
Column name or number of supplied values does not match table definition.

Below is the Stored Procedure

USE [LMGCRM]
GO
/****** Object:  StoredProcedure [dbo].[assgnzambtautoleadsforRoundtest]    Script Date: 2019/09/30 11:16:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[autotramenlunallocated]
AS
BEGIN
Insert into unactionedautotraders 
Select * from autotr_2menl p
        left  join customers C
        ON C.code=p.Ref
where c.contacted IS NULL and p.DaySS >='1'
END
GO

Answer 1

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.

Answer 2

CREATE PROCEDURE [dbo].[autotramenlunallocated]
AS
BEGIN
Insert into unactionedautotraders(column1, column2, someInt, someVarChar )---------Select Column that u want to insert into that 
Select p.column1,p.column2,C.someInt,C.someVarChar from autotr_2menl p    ---------Select Same No. of column and data type as above Line...
        left  join customers C
        ON C.code=p.Ref
where c.contacted IS NULL and p.DaySS >='1'
END

Note:- INSERT INTO SELECT requires that data types in source and target tables match.