Need help to built Insert statement into Table

Question

Need help to built Insert statement into Table

Qira

SSCommitted

Points: 1930
More actions
November 1, 2015 at 1:01 am

#399258

I've 2 tables as follow, --> Full script and data as attachment, Scripts.zip
CREATE TABLE [dbo].[myMenuCollection](
[menuCollection_idx] [int] NOT NULL,
[parentID] [int] NULL,
[menuNme] [nvarchar](200) NULL,
[navigateURL] [nvarchar](100) NULL,
CONSTRAINT [PK_myMenuCollection] PRIMARY KEY CLUSTERED
(
[menuCollection_idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
CREATE TABLE [dbo].[myInsertedMenu](
[idx] [int] IDENTITY(1,1) NOT NULL,
[menuCollection_idx] [int] NULL
) ON [PRIMARY]
Below is my Web Application
You can see - User select 3 Menu, which is the Menu Id is 1, 4, 10.
If the Parent Id for Menu is 0, there is 1 record only to insert.
If the Parent Id for Menu != 0, we've to make sure the Insert statement will insert the Parent Menu automatically
Based on Photo Above, there's 3 Menu is selected. But, in back-end - Insert statement will insert 4 record. Please see Menu Id = 10. The Parent Id = 9. So, we need to insert Menu Id = 9 automatically into myInsertedMenu table
Please help me to built T-SQL
insert into myInsertedMenu (menuCollection_idx)
select ????????
Please see Visual Communication as attachment, Visual Communication.zip
Please, I'm stuck

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

If you'll never have more than two levels, the following SQL will do as you ask (GREAT JOB on providing test tables and data... thanks)...

INSERT INTO dbo.myInsertedMenu

(menuCollection_idx)

SELECT menuCollection_idx = ca.mixedID

FROM dbo.myMenuCollection mmc

CROSS APPLY (SELECT mmc.menuCollection_idx UNION ALL SELECT mmc.parentID) ca (mixedID)

WHERE mmc.menuCollection_idx IN (1,4,10)

AND ca.mixedID > 0

;

The CROSS APPLY does an UNPIVOT of the menuCollection_idx and the related parentID and then the WHERE clause filters out the zero's.

The IN() in the WHERE clause would need to be parameterized. I don't know much about front-end code anymore so can't help you there.

--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)

Qira SSCommitted Points: 1930 More actions · Answer 2

Hello Jeff,

Your scripts is accurate.

INSERT INTO dbo.myInsertedMenu

(menuCollection_idx)

SELECT menuCollection_idx = ca.mixedID

FROM dbo.myMenuCollection mmc

CROSS APPLY (SELECT mmc.menuCollection_idx UNION ALL SELECT mmc.parentID) ca (mixedID)

WHERE mmc.menuCollection_idx IN (1,4,10)

AND ca.mixedID > 0

;

However, I have menu more than 1 level. See below,

This is All Menu. See Menu Id (Primary Key), and Parent Id

This is - 1 Level. So, I can using your script without problem

Unfortunately - I've menu more than 1 level. See below. Menu Id = 11. Parent Id = 10. But Parent also have Parent

I using your script

SELECT menuCollection_idx = ca.mixedID

FROM dbo.myMenuCollection mmc

CROSS APPLY (SELECT mmc.menuCollection_idx UNION ALL SELECT mmc.parentID) ca (mixedID)

WHERE mmc.menuCollection_idx IN (1,4, 11)

AND ca.mixedID > 0

/*

menuCollection_idx

------------------------

1

4

11

10

*/

Looks like, Menu Id = 9 is missing

Please help

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 3

So you need traverse menu hierarchy . Under above setup the

hierarchy CTE will do.

with hc as (

select menuCollection_idx, parentID

from [myMenuCollection]

where menuCollection_idx in (1,4,11)

union all

select mc.menuCollection_idx, mc.parentID

from [myMenuCollection] as mc

join hc on hc.parentID = mc.menuCollection_idx

)

select distinct menuCollection_idx, parentID

from hc;

You may wish to read more about CTE and hierachy at http://www.sqlservercentral.com/articles/T-SQL/65540/

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

I agree with Serg. The rCTE method seems appropriate here. I'd lose the DISTINCT, though. Ostensibly, the child ID's are unique already.

Shifting gears a bit, if you have a much larger hierarchy, other methods may be more appropriate for reasons of performance and utility. For those, please see the following articles.

http://www.sqlservercentral.com/articles/Hierarchy/94040/

http://www.sqlservercentral.com/articles/T-SQL/94570/

--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)