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.

You might want to take peek at this. sqlblog.org/2011/09/20/… And I would suggest adding comments to your code when you have functions like. Makes maintenance a lot easier. — Sean Lange
– Sean Lange, Commented Apr 30, 2018 at 20:15

Brad · Accepted Answer · 2018-04-30 19:56:21Z

8

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

answered Apr 30, 2018 at 19:56

Brad

3,7253 gold badges32 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Krishnang K Dalal Over a year ago

Ah, got it. It worked. Thank you. My first attempt at stored procedures.I'll accept your answer in few minutes.

Collectives™ on Stack Overflow

Insert values into a table using stored procedure without parameters

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related