• Without a table alteration, you could do this (using Matt's fine test setup)...

    --testing setup

    create table #recipe (recipeID int identity(1,1) primary key, recipename varchar(200))

    create table #recipeline(reclineID int identity(1,1) primary key, recipeID int, itemid int)

    insert #recipe( recipename)

    select 'chicken' union all

    select 'pizza'

    insert #recipeline(recipeid,itemID)

    select 1,20 union all

    select 1,34 union all

    select 1,70 union all

    select 1,50 union all

    select 2,60 union all

    select 2,13 union all

    select 2,28

    go

    --===== Remember the current max recipe ID

    DECLARE @MaxRecipeID INT

    SELECT @MaxRecipeID = MAX(RecipeID)

    FROM #Recipe

    --===== Duplicate the rows in the Recipe table

    SET IDENTITY_INSERT #Recipe ON

    INSERT INTO #Recipe (RecipeID,RecipeName)

    SELECT RecipeID = RecipeID + @MaxRecipeID,

    RecipeName = RecipeName

    FROM #Recipe

    SET IDENTITY_INSERT #Recipe OFF

    --===== Duplicate the rows in the RecipeLine table

    INSERT INTO #RecipeLine(RecipeID,ItemID)

    SELECT RecipeID = RecipeID + @MaxRecipeID,

    ItemID = ItemID

    FROM #RecipeLine

    --===== Display the results

    SELECT * FROM #Recipe

    SELECT * FROM #RecipeLine

    --===== Cleanup the demo code

    DROP TABLE #Recipe, #RecipeLine

    Now, tell us why you want to do this, please? Thanks...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)