How to INSERT data into multiple columns from multiple queries?

Question

This SQL Server stored procedure takes four parameters to show a table order by dates between FromDate and ToDate as follows:

CREATE PROCEDURE GetLedger
     (@FromDate date,  
      @ToDate date,  
      @Supplier int,  
      @MOP int)
AS  
BEGIN  
    DECLARE @ExpTABLE TABLE 
                      (  
                          RowNo int,  
                          TranDate date,  
                          NetExpense float
                      )  

    INSERT INTO @ExpTABLE   
        SELECT 
            ROW_NUMBER() OVER (ORDER BY TranDate), TranDate, SUM(NetAmount) 
        FROM 
            Expenditure 
        WHERE
            TranDate BETWEEN @FromDate AND @ToDate 
            AND SupplierID = @Supplier 
            AND MOP = @MOP 
        GROUP BY 
            TranDate  

    DECLARE @Data TABLE
                  (  
                      RDate DATE,  
                      Expense float       
                      PRIMARY KEY (RDate)  
                  )  

    WHILE (@FromDate <= @ToDate)  
    BEGIN  
        INSERT INTO @Data (RDate) 
        VALUES (@FromDate)  

        SELECT @FromDate = DATEADD(DAY, 1, @FromDate)  
    END 

    WHILE (@FromDate <= @ToDate)  
    BEGIN 
        INSERT INTO @Data (Expense) 
            SELECT NetExpense 
            FROM @ExpTABLE 
            WHERE TranDate = @FromDate 

        SELECT @FromDate = DATEADD(DAY, 1, @FromDate)
    END

    SELECT * FROM @Data
END
--EXEC GetLedger '2020-03-01' ,'2020-03-31',2,2

The data in @ExpTABLE is like this:

RowNo   TranDate    NetExpense
------------------------------
1       2020-03-15  35

Now, I am trying to INSERT this NetExpense of '2020-03-15' in @Data Table in the respective date while the rest NetExpense of Other dates remain null.

This INSERT query:

WHILE (@FromDate <= @ToDate)  
BEGIN 
    INSERT INTO @Data (Expense) 
        SELECT NetExpense 
        FROM @ExpTABLE 
        WHERE TranDate = @FromDate 

    SELECT @FromDate = DATEADD(DAY, 1, @FromDate)
END

is inserting NULL in all dates including '2020-03-15'. What am I missing here?

this is a) hopeless and b) unlikely to be of any usefulness to any future readers. To give you a broad idea, relational databases are not used like that, and the INSERT command does not work like that. — Mike Nakis, Mar 21 '20 at 12:42
Thanks @MikeNakis, Would you like to elaborate the proper way to achieve what I need? — Opengenic, Mar 21 '20 at 12:49
change insert to `INSERT INTO @Data(RDate, Expense) SELECT TranDate, NetExpense from @ExpTABLE where TranDate = @FromDate `. This will create 1 record with both rDate and Expense set. This is only slightly optimizing the two WHILE loops in your code. For a better solution: No WHILE loop is needed, just 1 INSERT statement will do.... — Luuk, Mar 21 '20 at 12:50
@Luuk, But then the @Data Table would not have all the Dates between `FromDate` and `ToDate`? — Opengenic, Mar 21 '20 at 12:54
This link [DOCS](https://learn.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver15) uses only 33 minutes of your time, understanding what is being said maybe longer.... — Luuk, Mar 21 '20 at 12:57
I don't understand what you are trying to do. You already have a query at the very start that uses a bulk insert to populate a table without a while loop. Why then go and do it in a while loop? Is it because you want to fill in missing dates? — Nick.Mc, Mar 21 '20 at 13:05
@Nick.McDermaid, Yes. I wanted all the dates between the parameters and insert `NetExpense` from `ExpTABLE ` in the respective dates. — Opengenic, Mar 21 '20 at 13:10
It is always helpful to take a step back and explain what your objective is rather than just asking for your code to be fixed. Perhaps there is a completely different way to do what you need. — Isaac, Mar 21 '20 at 13:11
@Isaac, I wanted all the dates between the parameters and insert `NetExpense` from `ExpTABLE` in the respective dates of `Data` Table. I just want a general idea what's wrong I'm doing. — Opengenic, Mar 21 '20 at 13:14
It comes down to set-based vs. procedural code. Rather than trying to explain that I'll point you [to](https://ask.sqlservercentral.com/questions/2019/set-based-vs-procedural.html) [some](https://www.sqlshack.com/introduction-set-based-vs-procedural-programming-approaches-t-sql/) [resources](https://stackoverflow.com/questions/24168/why-are-relational-set-based-queries-better-than-cursors). I am working on an answer for you too. — Isaac, Mar 21 '20 at 13:20

score 0 · Answer 1 · edited Mar 28 '20 at 09:57

In order to have a row for every date in your range it would be helpful to use a Calendar table. There are lots of resources available on how to do that like this one. Another option is to create your a calendar table on the fly as a common table expression (CTE) like this.

When developing a stored procedure I like to get it working just as a regular SQL statement and once I have that I can transform it into a stored procedure. So here is the regular SQL statement that should get you going.

DECLARE
    @FromDate DATE
  , @ToDate   DATE
  , @Supplier INT
  , @MOP      INT;

-- set these values to whatever you would pass to your stored procedure
SET @FromDate = '2020-03-01';
SET @ToDate = '2020-03-31';
SET @Supplier = 2;
SET @MOP = 3;

DECLARE @Expenditure TABLE
(
    ID INT NOT NULL
  , TranDate DATE NOT NULL
  , SupplierID INT NOT NULL
  , MOP INT NOT NULL
  , NetAmount DECIMAL(10, 2) NOT NULL
);

INSERT INTO @Expenditure (ID, TranDate, SupplierID, MOP, NetAmount)
VALUES
  (1, '02/28/2020', 2, 3, 200.12)
, (2, '03/15/2020', 2, 3, 125.10)
, (3, '03/15/2020', 2, 3, 74.90)
, (4, '03/17/2020', 3, 3, 150.32)
, (5, '03/18/2020', 2, 3, 250.78)

;WITH cteCalendar (MyDate) AS
(
    SELECT CONVERT(DATE, @FromDate) AS MyDate
    UNION ALL
    SELECT DATEADD(DAY, 1, MyDate)
    FROM   cteCalendar
    WHERE  DATEADD(DAY, 1, MyDate) <= @ToDate
)
SELECT
     ROW_NUMBER() OVER (ORDER BY x.TranDate) AS [RowNo]
   , x.TranDate
   , x.NetExpense
FROM (
         SELECT
                   c.MyDate         AS TranDate
                 , SUM(e.NetAmount) AS [NetExpense]
         FROM      cteCalendar  c
         LEFT JOIN @Expenditure e ON c.MyDate = e.TranDate
                                     AND e.SupplierID = @Supplier
                                     AND e.MOP = @MOP
         GROUP BY  c.MyDate
     ) x;

Here is a demo of the code.

That worked like a charm! I realized what I am doing wrong. And the references were very helpful. Thank you! — Opengenic, Mar 21 '20 at 16:16

How to INSERT data into multiple columns from multiple queries?

1 Answers1