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

You’ll need MS SQL 2005 or above, it looks like version 2012 has it built in see here!
This is my first use of the OVER bit so it may be a little ugly, also I have been using the old way to pivot stuff so it’s a bit messy at the end.

declare @t table (percentile numeric(10,5))
insert into @t values (0)
insert into @t values (0.25)
insert into @t values (0.5)
insert into @t values (0.75)
insert into @t values (0.8)
insert into @t values (1.0)

--give rownumbers for your data over a partition
drop table ##mgw
SELECT 
	s.gppraccode
	,age
	,ROW_NUMBER() OVER (partition by gppraccode ORDER BY age) AS "RowNumber"
INTO ##mgw
FROM mgw_ane_base AS s 

--calculate the individual values per percentile
drop table ##mgw_percentileHolding
select	t.percentile*(max(rownumber)-1)+1 as act
		,floor(t.percentile*(max(rownumber)-1)+1) as K
		,((t.percentile*(max(rownumber)-1)+1) % 1) as D
		,count(*) as N
		,t.percentile
		,a.gppraccode
into ##mgw_percentileHolding
from ##mgw as a join @t as t on 1=1
group by t.percentile,a.gppraccode

--fold it all over in a manual-ish pivot
select 
	gppraccode
	,max(case when percentile=0 then PercentileValue end) as [0%]
	,max(case when percentile=0.25 then PercentileValue end) as [25%]
	,max(case when percentile=0.5 then PercentileValue end) as [50%]
	,max(case when percentile=0.75 then PercentileValue end) as [75%]
	,max(case when percentile=0.8 then PercentileValue end) as [80%]
	,max(case when percentile=1 then PercentileValue end) as [100%]
	,N
FROM
	(
	SELECT 
	*
	,(SELECT max(age) from ##mgw as b where b.rownumber<=a.act and a.gppraccode=b.gppraccode) as below
	,(SELECT min(age) from ##mgw as b where b.rownumber>=a.act  and a.gppraccode=b.gppraccode) as above
	,(SELECT min(age) from ##mgw as b where b.rownumber>=a.act  and a.gppraccode=b.gppraccode)-(select max(age) from ##mgw as b where b.rownumber<=a.act and a.gppraccode=b.gppraccode ) as f
	,case	when percentile=0 then (SELECT min(age) from ##mgw as b where a.gppraccode=b.gppraccode) 
			when percentile=1 then (SELECT max(age) from ##mgw as b where a.gppraccode=b.gppraccode)  
			else (SELECT max(age) from ##mgw as b where b.rownumber<=a.act and a.gppraccode=b.gppraccode)+D*((select min(age) from ##mgw as b where b.rownumber>=a.act and a.gppraccode=b.gppraccode)-(select max(age) from ##mgw as b where b.rownumber<=a.act and a.gppraccode=b.gppraccode)) end as PercentileValue
	FROM ##mgw_percentileHolding as a
) as internal
group by gppraccode,N

You need SQL 2005 or greater for this:
Rownumbers with partitions:

SELECT 
    s.gppraccode
    ,age
    ,ROW_NUMBER()OVER (PARTITION BY gppraccode ORDER BY age asc) As [RowNumber]
FROM mgw_ane_base AS s 

SELECT s.gppraccode, age
    ,ROW_NUMBER() OVER (ORDER BY age) AS "RowNumber"
    ,RANK() OVER (ORDER BY age) AS Rank
    ,DENSE_RANK() OVER (ORDER BY age) AS "DenseRank"
    ,NTILE(10) OVER (ORDER BY age) AS Quartile
INTO ##mgw
FROM mgw_ane_base AS s 

UPDATE Your_table_name_here
SET Your_field_name_here = your_value_to_update_here
WHERE your_logical_condition_here = this_value