update the main table and several sub tables using the autonumber primary key from the first table…

Posted on by

You’ve got a table (here bed_beds with pk = bed_refno) and you want to insert a new ‘bed’ and update the bed_beds_specialties and bed_sex_status tables using the newly created auto-generated id key.

Create a Stored Procedure (SP) and use the the SCOPE_IDENTITY() function to keep a record of the identity key generated within the scope. Then pass that to the INSERT queries used further down in the SP.


CREATE PROCEDURE spInsert_Bed
@wardRefno numeric(10,0),
@typeRefno numeric(10,0),
@dStart DATETIME,
@dEnd DATETIME,
@dCreate DATETIME,
@vUser VARCHAR(15)
AS
SET NOCOUNT ON
declare @lastin numeric(10,0)
INSERT INTO bed_beds (ward_refno, type_refno, start_Dttm, end_Dttm,create_Dttm,user_create)
VALUES (@wardRefno, @typeRefno, @dStart, @dEnd,@dCreate,@vUser)
set @lastin=  SCOPE_IDENTITY()

INSERT INTO bed_beds_specialties (bed_refno, spect_refno,comments, start_Dttm, end_Dttm,create_Dttm,user_create)
VALUES (@lastin, 3,'my sp test', @dStart, @dEnd,getdate(),@vUser)

INSERT INTO bed_sex_status (bed_refno, start_Dttm, end_Dttm,sex_rfval_refno,create_Dttm,user_create)
VALUES (@lastin,  @dStart,@dEnd,6,getdate(),@vUser)

GO

Leave a Reply