Insert values into a table using stored procedure without parameters

Question

I want to write a stored procedure that inserts data in a table by joining two tables. Below is my attempt but I'm getting an error.

CREATE PROCEDURE [dbo].[stored_proc1] 
AS
BEGIN 
    INSERT INTO [dbo].[IN_TABLE]
        SELECT 
            l.col1, l.col2, l.col3, l.col4,
            r.col1, r.col2
        FROM 
            db2.dbo.table1 AS l
        LEFT JOIN 
            dbo.[table2] AS r ON l.col1 = r.col2
        WHERE 
            l.col4 >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), -7);

    DELETE FROM dbo.[IN_TABLE] 
    WHERE col4 < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()),-7);
END
GO

This is the error I get:

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

All the examples I came across while searching for the solution to my task use parameters and insert them by setting them to a specific value which doesn't help me since I want to insert the out of a SELECT statement.

I would much appreciate your help. Thank you in advance.

Answer 1

you need to specify the columns you are inserting. Without knowing your list of fields in the In_Table cant post exact code but here is what you need tod o

        INSERT INTO [dbo].[IN_TABLE] (
    col1, col2, col3, etc  (this lists needs to match your list of fields in your select
    )
SELECT l.col1
          ,l.col2
          ,l.col3
          ,l.col4 
          ,r.col1
          ,r.col2
FROM db2.dbo.table1 AS l
LEFT JOIN dbo.[table2] AS r
        ON l.col1= r.col2
WHERE l.col4 >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()),-7);