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.
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
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.