CODEWITHSUNDEEP
sql-server
TechRon
TechRon

Reputation: 37

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

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.

Upvotes: 2

Views: 774

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46203

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

Upvotes: 2

Kaval Patel
Kaval Patel

Reputation: 668

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.

Upvotes: 0

Related Questions