Sql-server – INSERT from SELECT using a CURSOR

cursorsinsertsql-server-2005

I have a table that looks like this:

+====================================================+
| aid  |  uid  |  eid  |  name  |    dob     | paid  |
+====================================================+
|  1   |  100  |  10   |  Mick  | 1980-03-29 |  40   |
|  2   |  101  |  11   |  Jack  | 1969-11-21 |  40   |
|  3   |  205  |  11   |  Lynn  | 1990-07-10 |  80   |
|  4   |  217  |  10   |  Dana  | 1987-02-15 |  40   |
+----------------------------------------------------+

Where aid = Temp table identity column, uid = User table FK, eid = Employer table FK

So this data needs to go into another table but it would look something like this:

+====================================================+
|  id  |  uid  |  eid  |  week  |  postdate  | paid  |
+====================================================+
|  1   |  100  |  10   |    1   | 2015-09-03 |  10   |
|  2   |  100  |  10   |    2   | 2015-09-03 |  10   |
|  3   |  100  |  10   |    3   | 2015-09-03 |  10   |
|  4   |  100  |  10   |    4   | 2015-09-03 |  10   |
|  1   |  101  |  11   |    1   | 2015-09-03 |  10   |
|  2   |  101  |  11   |    2   | 2015-09-03 |  10   |
|  3   |  101  |  11   |    3   | 2015-09-03 |  10   |
|  4   |  101  |  11   |    4   | 2015-09-03 |  10   |
|  1   |  205  |  11   |    1   | 2015-09-03 |  20   |
|  2   |  205  |  11   |    2   | 2015-09-03 |  20   |
|  3   |  205  |  11   |    3   | 2015-09-03 |  20   |
|  4   |  205  |  11   |    4   | 2015-09-03 |  20   |
|  1   |  217  |  10   |    1   | 2015-09-03 |  10   |
|  2   |  217  |  10   |    2   | 2015-09-03 |  10   |
|  3   |  217  |  10   |    3   | 2015-09-03 |  10   |
|  4   |  217  |  10   |    4   | 2015-09-03 |  10   |
+----------------------------------------------------+

So whats going on is, I pick a month of the year and then I get the week numbers for that month (week column) and for each record in my initial table I add one to my second table.
Mick, Jack and Dana paid 40 (split 4 [weeks of the month]) and Lynn paid 80 thus making it to 20 per week of the selected month.

So basically I would like to ask if THIS or THIS type of method using a CURSOR is what I'm looking for or is there something more specific toward inserting records from a SELECT for each record in the table (no more than 600(x4) records at the time).

On a side note the examples just considering "moving" or replicating the records from one table to the other, so 100 records here become 100 records there. What options can I implement to insert 4 records per each one on my temp table (other than loops on my frontend code) using SQL?

Best Answer

Below is an example that uses a recursive CTE to create weekly values and a simple INSERT...SELECT to populate your table. I assumed a few identity IDs for testing's sake in addition to the postdate being the current date. You don't need a cursor to do this work. Enjoy!

    --CREATE SCENARIO TEMP TABLE
    IF OBJECT_ID('tempdb..#TempUserPaid','U') IS NOT NULL
        DROP TABLE #TempUserPaid

    CREATE TABLE #TempUserPaid
    (
          aid INT IDENTITY
        , uid INT
        , eid INT
        , name VARCHAR(50)
        , dob DATE
        , paid DECIMAL(19,4)
    )

    INSERT #TempUserPaid
    VALUES
         (100,10,'Mick','1980-03-29',40.00)
        ,(101,11,'Jack','1969-11-21',40.00)
        ,(205,11,'Lynn','1990-07-10',80.00)
        ,(217,10,'Dana','1987-02-15',40.00)


    --CREATE WEEKLYPAYMENTS TABLE IF IT DOESN'T EXIST TO MODEL USER TABLE
    --DROP TABLE WeeklyPayments  --> CLEAR OUT FOR TESTING PURPOSES
    IF OBJECT_ID('WeeklyPayments','U') IS NULL
        CREATE TABLE WeeklyPayments
        (
              id INT IDENTITY
            , uid INT
            , eid INT
            , [week] INT
            , postdate DATE
            , paid DECIMAL(19,4)
        );

    --INSERT YOUR CODE TO GENERATE NUMBER OF WEEKS HERE TO SET @weeksInMonth
    --HARDCODING FOR THIS EXAMPLE
    DECLARE @weeksInMonth SMALLINT;
    SET @weeksInMonth = 4;


    --RECURSIVE CTE TO GENERATE THE NUMBER OF WEEKS
    WITH cteWeeks AS
    (
        SELECT 1 WeekInMonth
        UNION ALL
        SELECT WeekInMonth+1 
        FROM cteWeeks
        WHERE WeekInMonth < @weeksInMonth
    )
    INSERT WeeklyPayments
    SELECT t.uid, t.eid, c.WeekInMonth, CONVERT(DATE,GETDATE()), CAST(t.paid/4 AS money)
    FROM #TempUserPaid t, cteWeeks c
    ORDER BY t.uid;

    SELECT * FROM WeeklyPayments;
Related Question