Advanced Query - Insert Statement

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.

CODE:


WHILE (@Start_Year <= @End_Year)


BEGIN

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]

)

VALUES

(

    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

END;


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?

Greetings!

  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}.[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) t

Notice that there I didn't use VALUES.