Gettind newly generated ID from user defined table type and inserting it into table with more data

Sorry in advance for my naive heading

my problem is
I have a user-defined table type

  • (ORDER BY CASE WHEN) ordering by subquery
  • Multiple aggregate functions in one SQL query from the same table using different conditions
  • Sum two columns that corresponds a agregate from a columns of two tables
  • Doing a join across two databases with different collations on SQL Server and getting an error
  • T-SQL Recursive Group for Parent Child
  • SQL select from column where column equals table variable?
  • CREATE TYPE [dbo].[CldDetails] AS TABLE
    (
    ScheduledDate         DATETIME,
    Amount                MONEY,
    pmtTypeId             INT,
    StatusId              INT,
    UpdatedDate           DATETIME
    reference             Varchar(50)
    )
    

    I am taking that table in my stored procedure as input parameter

    CREATE PROCEDURE [dbo].[AldAdd]
    @clientDrafts                     [dbo].[CldDetails]  READONLY
    AS
    BEGIN
    INSERT INTO [dbo].CPTDetail (cppId, Date, Amount, pmtTypeId, StatusId,    
    UpdatedDate)
    SELECT ScheduledDate, Amount, pmtTypeId, StatusId, UpdatedDate FROM  
    @clientDrafts;
    END
    

    Now my problem is that i need to insert a new record for each ID generated in above insert into another table with
    reference field in @clientDrafts. Suppose there are 4 records in @clientDrafts. record no 1 has reference 10007.Now for first record new id 1 is generated then
    i need to get that newly generated id and reference 10007 from @clientDrafts into another table.

    2 Solutions collect form web for “Gettind newly generated ID from user defined table type and inserting it into table with more data”

    Because you are not inserting the reference value into the target table, it will not be available in the OUTPUT clause of INSERT...SELECT to correlate the generated cppId column IDENTITY value with the reference value. However, you can do this by using MERGE instead, assuming you are using a modern SQL Server version.

    ALTER PROCEDURE [dbo].[AldAdd]
        @clientDrafts [dbo].[CldDetails] READONLY
    AS
    
    MERGE [dbo].CPTDetail AS target
    USING (SELECT
          ScheduledDate
        , Amount
        , pmtTypeId
        , StatusId
        , UpdatedDate
        , reference
        FROM @clientDrafts) AS source ON 0 = 1 --unconditional inserts
    WHEN NOT MATCHED THEN 
        INSERT (
            Date
            , Amount
            , pmtTypeId
            , StatusId
            , UpdatedDate
            )
        VALUES (
            source.ScheduledDate
            , source.Amount
            , source.pmtTypeId
            , source.StatusId
            , source.UpdatedDate
        )
    OUTPUT
            inserted.cppId --generated identity value
          , source.ScheduledDate
          , source.Amount
          , source.pmtTypeId
          , source.StatusId
          , source.UpdatedDate
          , source.reference
          INTO dbo.AnotherTable;
    GO
    

    Please try this one:
    Use @@IDENTITY for newly created record this will give you id for last inserted record I will give you sample SQL script please try to modify that according to your tables.
    One more this you required using cursor fetch record one by one.

    DECLARE @Id decimal(18,0)
        DECLARE @newId decimal(18,0)
    DECLARE Cur CURSOR FOR SELECT pId FROM  temp
    
    OPEN Cur 
    FETCH NEXT FROM Cur INTO @Id
    
    WHILE ( @@FETCH_STATUS = 0 )
    
    BEGIN
    
     --------------Insert into table 1
                    INSERT INTO temp (pId,Name)
                    SELECT pId ,Name from @clientDrafts where pId = @Id 
    
    SET @newId = @@IDENTITY
    
     --------------Insert into table 1
                    INSERT INTO temp2
                    (Id,Name,pId)
                    Select Id,Name,pId=@newId from temp2 
                        where 
                            pId = @Id
    
    FETCH NEXT FROM Cur INTO @Id
    
    END
    
    CLOSE Cur 
    DEALLOCATE Cur
    

    please try this it will help you.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.