0

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?

3

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;
  • Sweet! Yes, and implementation of this method is what I'll end up using, Thanks for your reply! – Nelz Sep 4 '15 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.