CODEWITHSUNDEEP
sqlsql-server
user3378165
user3378165

Reputation: 6896

Looping through specific values on a table and insert new rows

I have the following table: 

ID, UserID, CompanyID, AccountID, Year1, Month1

I need to insert 10 rows to each AccountID, is there a way to loop through all AccountIDs and to insert for each one of them the following values?

INSERT INTO Perms (UserID, CompanyID, AccountID, Year1, Month1)
VALUES
    (175, 74,x,2017,3),
    (175, 74,x,2017,4),
    (175, 74,x,2017,5),
    (175, 74,x,2017,6),
    (175, 74,x,2017,7),
    (175, 74,x,2017,8),
    (175, 74,x,2017,9),
    (175, 74,x,2017,10),
    (175, 74,x,2017,11),
    (175, 74,x,2017,12)

I have about 100 AccountIDs and I need some sort of a loop.

Is that doable?

Upvotes: 2

Views: 63

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

Use CTEs to represent the account and date sequences. In the case of the account ID values, we can use a recursive CTE. Below I arbitrarily generate values from 1 to 100, though this approach should work with any continuous range. For the year/month combinations, because there are only 10 we can simply hard code them in a CTE. Then, use INSERT INTO ... SELECT with a cross join of the two CTEs.

WITH accounts AS ( 
    SELECT 1 AS account
    UNION ALL 
    SELECT account + 1 
    FROM accounts 
    WHERE account + 1 <= 100
),
cte AS (
    SELECT 2017 AS year, 3 AS month UNION ALL
    SELECT 2017, 4 UNION ALL
    SELECT 2017, 5 UNION ALL
    SELECT 2017, 6 UNION ALL
    SELECT 2017, 7 UNION ALL
    SELECT 2017, 8 UNION ALL
    SELECT 2017, 9 UNION ALL
    SELECT 2017, 10 UNION ALL
    SELECT 2017, 11 UNION ALL
    SELECT 2017, 12
)
INSERT INTO Perms (UserID, CompanyID, AccountID, Year1, Month1)
SELECT 175, 74, account, year, month
FROM accounts
CROSS JOIN cte;
OPTION (MAXRECURSION 255);

Edit:

If your account IDs are not continuous, then continuing with this answer you may just manually list them in a CTE, e.g.

WITH accounts AS ( 
    SELECT 71 AS account UNION ALL
    SELECT 74 UNION ALL
    SELECT 78 UNION ALL
    SELECT 112 UNION ALL
    SELECT 119
    -- and others
)

Upvotes: 2

Michał Turczyn
Michał Turczyn

Reputation: 37347

Try this. This is very similair to already existing answer, but more compact:

;with cte as (
  select 175 [UserID], 74 [CompanyID], 2017 [Year1], 3 [Month1]
  union all
  select 175 [UserID], 74 [CompanyID], 2017 [Year1], [Month1] + 1 from cte
  where [Month1] < 12
)
select A.[UserID], A.[CompanyID], B.[AccountID], A.[Year1], A.[Month1] from cte A cross join TABLE_NAME B

Upvotes: 1

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

If you have the accountId's stored in a table and what you want is to insert 10 rows for each account id with Month1 from 3 to 12, try this

WITH CTE
AS
(
    SELECT
        Month2 = 1

    UNION ALL

    SELECT
        Month2+1
        FROM CTE
            WHERE Month2 <12
)
INSERT INTO Perms (UserID, CompanyID, AccountID, Year1, Month1)
SELECT
    UserID = 175, 
    CompanyID ='X', 
    AccountID = YAT.AccountID, 
    Year1 = 2017, 
    Month1 = CTE.Month2
    FROM CTE
        INNER JOIN YourAccountTable YAT
            ON CTE.Month2 BETWEEN 3 AND 12

Change the between clause if you want diffrent values

Upvotes: 0

Related Questions