Adding an ID from the parent table

Question

When updating two tables with the cursor, insert the ID of the parent table into the child table. I am using a solution using the SCOPE_IDENTITY function and I get an error after I group the records.

DECLARE @Table1 TABLE (
        id          INT
        ,custId     INT
        ,MonthVal   INT
        ,Code1      INT
        ,Code2      INT
        ,Val1       INT
        ,Val2       INT
    );

INSERT  INTO @Table1 (id
                    ,custId
                    ,MonthVal
                    ,Code1
                    ,Code2
                    ,Val1
                    ,Val2)
VALUES (11, 1, 10, 1500, 201, 1, 10)
    ,(12, 1, 10, 1500, 301, 2, 20)
    ,(13, 1, 11, 2000, 301, 3, 30)
    ,(14, 1, 10, 1500, NULL, 5, 50)
    ,(15, 1, 10, 1500, NULL, 5, 50);

DECLARE @TableCode1 TABLE (
    id          INT IDENTITY(1, 1)
    ,MonthVal   INT
    ,Code1      INT
    ,Val        INT
);

DECLARE @TableCode2 TABLE (
    id          INT IDENTITY(1, 1)
    ,Table1Id   INT
    ,Code2      INT
    ,Val        INT
);

DECLARE @MonthVal   INT;
DECLARE @Code1 INT;
DECLARE @Table1Id INT;


DECLARE cursor_product CURSOR FOR
SELECT  DISTINCT
        MonthVal
        ,Code1
FROM    @Table1;

OPEN cursor_product;

FETCH NEXT FROM cursor_product
INTO @MonthVal
    ,@Code1;

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT  INTO @TableCode1 (MonthVal
                            ,Code1
                            ,Val)
    SELECT      MonthVal
                ,Code1
                ,SUM(Val1)
    FROM        @Table1
    WHERE       MonthVal = @MonthVal
                AND Code1 = @Code1
    GROUP BY    MonthVal
                ,Code1
                ,Code2;

    SET @Table1Id = SCOPE_IDENTITY();

    INSERT INTO @TableCode2 (Code2
                            ,Val
                            ,Table1Id)
    SELECT      Code2
                ,SUM(Val2)
                ,@Table1Id
    FROM        @Table1
    WHERE       MonthVal = @MonthVal
                AND Code1 = @Code1
    GROUP BY    MonthVal
                ,Code1
                ,Code2;

    FETCH NEXT FROM cursor_product
    INTO @MonthVal
        ,@Code1;
END;

CLOSE cursor_product;

DEALLOCATE cursor_product;

SELECT * FROM @TableCode1;
SELECT * FROM @TableCode2;

That's what I get: Table Code 1 everything is correct in this table:

TableCode2 in this table, in the Table1Id field, I expect to see the tablecode1 table IDs, but since I use scope_identity, I have the last ID added because the data was grouped in the TableCode1 table:

Expected result in the table TableCode2:

Answer 1

I think that you should remove column Code2 from GROUP BY there:

    INSERT  INTO @TableCode1 (MonthVal
                            ,Code1
                            ,Val)
    SELECT      MonthVal
                ,Code1
                ,SUM(Val1)
    FROM        @Table1
    WHERE       MonthVal = @MonthVal
                AND Code1 = @Code1
    GROUP BY    MonthVal
                ,Code1
                ,Code2;

Updated: I was try explain again, and think, that you should remove cursor and add this:

DECLARE @TableCode3 TABLE
(
    id       INT IDENTITY (1, 1),
    MonthVal INT,
    Code1    INT,
    Code2    INT,
    SumVal1  INT,
    SumVal2  INT
);

INSERT INTO @TableCode3 (MonthVal, Code1, Code2, SumVal1, SumVal2)
SELECT MonthVal, Code1, Code2, SUM(Val1), SUM(Val2)
FROM @Table1
GROUP BY MonthVal, Code1, Code2;

INSERT INTO @TableCode1 (MonthVal, Code1, Val)
SELECT MonthVal, Code1, SumVal1
FROM @TableCode3

INSERT INTO @TableCode2 (Code2, Val, Table1Id)
SELECT Code2, SumVal2, Id
FROM @TableCode3

The result is:

Answer 2

How's this?   - Select actual ID from table1 and also group by it. 

DECLARE @Table1 TABLE (
    id INT
    ,custid   INT
    ,MonthVal      INT
    ,Code1        INT
    ,Code2 INT
    ,Val1 INT
    ,Val2 INT
);

INSERT  INTO @Table1 (id
                    ,custId
                    ,MonthVal
                    ,Code1
                    ,Code2
                    ,Val1
                    ,Val2)
VALUES (1, 1, 10, 1500, 201, 1, 10)
    ,(2, 1, 10, 1500, 301, 2, 20)
    ,(3, 1, 11, 2000, 301, 3, 30)
    ,(4, 1, 10, 1500, NULL, 5, 50)
    ,(4, 1, 10, 1500, NULL, 5, 50);

DECLARE @TableCode1 TABLE (
    id          INT IDENTITY(1, 1)
    ,MonthVal   INT
    ,Code1      INT
    ,Val        INT
);

DECLARE @TableCode2 TABLE (
    id          INT IDENTITY(1, 1)
    ,Table1Id   INT
    ,Code2      INT
    ,Val        INT
);

DECLARE @MonthVal   INT;
DECLARE @Code1 INT;
DECLARE @Table1Id INT;


DECLARE cursor_product CURSOR FOR
SELECT  DISTINCT
        MonthVal
        ,Code1
FROM    @Table1;

OPEN cursor_product;

FETCH NEXT FROM cursor_product
INTO @MonthVal
    ,@Code1;

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT  INTO @TableCode1 (MonthVal
                            ,Code1
                            ,Val)
    SELECT      MonthVal
                ,Code1
                ,SUM(Val1)
    FROM        @Table1
    WHERE       MonthVal = @MonthVal
                AND Code1 = @Code1
    GROUP BY    MonthVal
                ,Code1
                ,Code2;

    SET @Table1Id = SCOPE_IDENTITY();

    INSERT INTO @TableCode2 (Code2
                            ,Val
                            ,Table1Id)
    SELECT      Code2
                ,SUM(Val2)
                ,Id
    FROM        @Table1
    WHERE       MonthVal = @MonthVal
                AND Code1 = @Code1
    GROUP BY    MonthVal
                ,Code1
                ,Code2
                ,Id;

    FETCH NEXT FROM cursor_product
    INTO @MonthVal
        ,@Code1;
END;

CLOSE cursor_product;

DEALLOCATE cursor_product;

SELECT * FROM @TableCode1;
SELECT * FROM @TableCode2;