Advanced Query - Insert Statement

I am working on a budgeting software.

I need to duplicate line item descriptions for every year that the budget is going to run for. (WHILE loop)

I do not want to duplicate it for the same year though (IF statement)

@Budget is the budget ID

@Start_Year is the start year for the budget

@End_Year is the end year for the budget

I could not even get the bolded INSERT statement to run on its own.


WHILE (@Start_Year <= @End_Year)


IF Count(SELECT {Budget_Line}.[Id] from {Budget_Line} where {Budget_Line}.[BudgetId] = @Budget and {Budget_Line}.[Year] = @Start_Year) > 0

INSERT INTO {Budget_Line}

( {Budget_Line}.[BudgetId]

, {Budget_Line}.[CategoryId]

, {Budget_Line}.[Year]




    SELECT @Budget, t.[Id], @Start_Year

    FROM (SELECT {Budget_Category}.[Id] from {Budget_Category} where {Budget_Category}.[BudgetId] = @Budget) as t


SET @Start_Year = @Start_Year + 1


Hi James,

Why don't you use Aggregates and Entity Create actions? are you expecting this to run over thousands of records? Or just a few at once?


  1. You shouldn't do a cycle inside an advanced query. That's what the For Each widget is for ;)
  2. The correct syntax for the bolded SQL statement would be:

INSERT INTO {Budget_Line}


, {Budget_Line}.[CategoryId]

, {Budget_Line}.[Year]


SELECT @Budget, t.[Id], @Start_Year

FROM (SELECT {Budget_Category}.[Id] from {Budget_Category} where {Budget_Category}.[BudgetId] = @Budget) t

Notice that there I didn't use VALUES.