DECLARE @LocationSource INT --The location we want to clone
DECLARE @LocationTarget INT --The newly cloned location

--===== Identify the loaction numbers involved in the cloning
SET @LocationSource = 1
SET @LocationTarget = 4

--===== Start a transaction because this all has to happen together
BEGIN TRANSACTION

--===== Get ALL the information we need at once including the new recipe ID's
;WITH
cteKnowAll AS
(
SELECT RecNumber = DENSE_RANK() OVER (ORDER BY s.RecId),
oldRecId =s.RecId,
s.[Name],
OldLocationID = s.LocationID,
NewLocationID = @LocationTarget,
i.ItemId
FROM Rec WITH (TABLOCKX), --Lock the table so no one can insert/update/delete
RecItem WITH (TABLOCKX) --Lock the table so no one can insert/update/delete
WHERE s.recId= s.recId
AND s.LocationID = @LocationSource
)
SELECT NewrecId = k.RecNumber +IDENT_CURRENT('rec'),
[Name] = k.[Name],
NewLocationID = k.NewLocationID,
itemId = k.ItemId
INTO #Clones
FROM cteKnowAll k

--===== Clone the rec
SET IDENTITY_INSERT recON
INSERT INTO rec
(recId,[Name],LocationID)
SELECT DISTINCT
NewrecId ,[Name],NewLocationID
FROM #Clones
SET IDENTITY_INSERT rec ON

--===== Clone the rec items
INSERT INTO recItem
(recId,ItemId)
SELECT DISTINCT NewrecId ,ItemId
FROM #Clones

--===== If we made it to here, we can commit
COMMIT

--===== Verify the original condition of the test tables
SELECT * FROM rec
SELECT * FROM recItem