Q:

How to use nested Insert based on select statement using ms sql server

I have multiple records in Select statement, I want to insert these records in two different tables based on Primary keys.
I have three tables Roles, MenuMaster and MenuChild as under
Roles:
 - RoleID(PK)  RoleName
 - 1        Sales
 - 2        Marketing
 - 3        IT
MenuMaster:
 - MID(PK)  MFormName  RoleID(FK)
 - 1        Orders        1
 - 2        SMM           2
 - 3        Help          1
MenuChild:
 - CID(PK)   FormName,          MID(FK)
 - 1         NewOrder.aspx        1
 - 2         RepeatOrder.aspx     1
 - 3         Advertise.aspx       2
I want to create a new merge Role like '4, Sales & Marketing' and copy all records of RoleID=1 and RoleID=2 from MenuMaster to 'Sales & Marketing' RoleID and copy  MenuChild record against new Inserted record MenuID

A:

Create the role and use a temp table to populate the other tables:
INSERT INTO dbo.Roles (RoleName)
VALUES ('Sales & Marketing')

SELECT 
    'Sales & Marketing' as RoleName,
    mm.MenuId,
    mm.MFormName,
    SCOPE_IDENTITY() AS PK
INTO #tmp
FROM dbo.MenuMaster mm
WHERE mm.RoleId in (1,2)

INSERT INTO dbo.MenuMaster (MFormName, RoleId)
SELECT t.MFormName, t.PK from #tmp t

UPDATE #tmp SET PK = SCOPE_IDENTITY()

INSERT INTO dbo.MenuChild(FormName, MenuId)
SELECT t.MFormName, t.PK from #tmp t

This is a little ugly because I'm not sure I completely understood your requirement. You can also use a CTE, but after the first INSERT into MenuMaster, you will need to create another CTE

