CODEWITHSUNDEEP
sqlsql-serverhierarchyadjacency-list
Ibo
Ibo

Reputation: 4309

Inserting hierarchal data into adjacency table

I have a hierarchy table:

CREATE TABLE tmp.myTable  
   (  
    [Id] int IDENTITY(1,1) PRIMARY KEY ,      
    [Desc] nvarchar(50) NOT NULL,
    [Lvl] TINYINT NOT NULL,
    [ParentId] int REFERENCES tmp.myTable(Id)
   )

I have to insert the data one by one, which can be time-consuming on a slow connection or over VPN etc

Let's say we want to insert this row of hierarchy:

   A > AB > ABC > ABCD

These are the statements:

   INSERT INTO tmp.myTable ([Desc],[Lvl],[ParentId]) VALUES('A',1,NULL);

   INSERT INTO tmp.myTable ([Desc],[Lvl],[ParentId])
   SELECT 'AB',2,Id FROM tmp.myTable WHERE [Desc]='A'

   INSERT INTO tmp.myTable ([Desc],[Lvl],[ParentId])
   SELECT 'ABC',3,Id FROM tmp.myTable WHERE [Desc]='AB'

   INSERT INTO tmp.myTable ([Desc],[Lvl],[ParentId])
   SELECT 'ABCD',4,Id FROM tmp.myTable WHERE [Desc]='ABC'

I was wondering if there is a better way to do this.

One idea is to make ID column INT and not IDENTITY and then control it on the application side, but I don't like that idea since if more than a user tries to do inserts, the transaction will fail or we may even make mistakes in inserting ParentIds

Upvotes: 0

Views: 70

Answers (1)

seanb
seanb

Reputation: 6685

This is not the only solution, but it is a reasonable method.

In your comments, you say that 'Descriptions are unique at each level, I have constraints for them'. If that is the case, you can set up your table with the Primary Key being (Desc, Lvl) rather than the arbitrary int you have now. This also means that the ParentID field is being changed to ParentDesc.

Then, whenever you insert data, you do insert the parent value directly (as @JohnCapelletti wisely imo suggests).

For the Foreign Key reference, it needs to reference both fields (Desc and Lvl). It will use ParentDesc, and for current ease of use, a calculated field called ParentLvl (these referring to the parent's Desc and Lvl respectively).

Note that I'm assuming for this task that parents are always one level up (e.g., if a row is level 3, then its parent is level 2). If parents may be multiple levels higher, or the calculated field is too annoying, you could make ParentLvl a normal field that you enter data into.

For example:

CREATE TABLE [myTable](
    [Desc] [nvarchar](50) NOT NULL,
    [Lvl] [tinyint] NOT NULL,
    [ParentDesc] [nvarchar](50) NULL,
    [ParentLvl]  AS (CONVERT([tinyint],[Lvl]-(1))) PERSISTED,
    PRIMARY KEY ([Desc], [Lvl])
    )
GO

ALTER TABLE [myTable]  WITH CHECK ADD  CONSTRAINT [FK_myTable_myTable] FOREIGN KEY([ParentDesc], [ParentLvl])
REFERENCES [myTable] ([Desc], [Lvl])
GO

ALTER TABLE [myTable] CHECK CONSTRAINT [FK_myTable_myTable]
GO

Then to insert data, you can insert with the parent desc directly specified e.g.,

INSERT INTO myTable ([Desc],[Lvl],[ParentDesc]) 
VALUES ('A',1,NULL),
       ('AB',2,'A'),
       ('ABC',3,'AB'),
       ('ABCD',4,'ABC');

This is what the data in the table then looks like.

|Desc   |Lvl    |ParentDesc |ParentLvl  |
|-------|-------|-----------|-----------|
|A      |1      |null       |0          |
|AB     |2      |A          |1          |
|ABC    |3      |AB         |2          |
|ABCD   |4      |ABC        |3          |

See dbfiddle here for a running example.

Upvotes: 1

Related Questions