Generating Identity Values for Existing Records of a Table in a Custom Order

Recently I needed to add an Identity column to a table which is already populated. Of course, adding a new column to a table with identity property can be done easily. But the problem is to generate values for the existing records in a custom order, as the values are getting generated automatically according to the order of the clustered index of the table.

So how do you add an identity column to a table and get the identity values generated for the existing records in a custom order? To Understand the problem let's create a table and populate it with some records.

Here a clustered index has been created on the InsertedDate column. Now let's populate it with some records.

And the results are as follows.

It is clear that records are getting ordered according to the InsertedDate column values due to the clustered index. Now let's add an identity column to the table and see how values are getting generated.

As shown, identity values for existing records get generated in the order of the clustered index. How do we get them according to the order of  the FirstName?

The method is to first load the records to a temporary table and truncate the table. and enable IDENTITY_INSERT property of the table ON. Then load the table according to the required order and set it back to OFF.

In this method, it is essential to use TRUNCATE TABLE command instead of  DELETE as other than deleting the records, resetting the identity property must also be done before inserting the ordered records.

If you can add the identity column in the middle of the data modification, there is no need to use this approach. But in my case, we were needed to separate the schema changes and data modifications into different files.

Full script of the above code can be downloaded here.

HTML Ordered List Directly From SQL

Recently I worked on a project that requires an unordered list to be prepared dynamically depending on many filters. Users are consistently generating the results with various filters and the results are shown as a list.

Well there are many ways to do this. You can simply get the results to client-side and construct the list using Javascript. But I thought of constructing the list using SQL as I’m anyway required to query a database.

To demonstrate what I did, let’s create a self-referenced table named “Menu” and add some data.

CREATE TABLE [dbo].[Menu](

       [MenuID] [int] NOT NULL,

       [ParentID] [int],

       [MenuName] [varchar](50) NOT NULL,

 CONSTRAINT [PK_Menu] PRIMARY KEY CLUSTERED

(

       [MenuID] ASC

))

ALTER TABLE [dbo].[Menu]  WITH CHECK ADD  CONSTRAINT [FK_Menu_Menu] FOREIGN KEY([ParentID])

REFERENCES [dbo].[Menu] ([MenuID])

INSERT INTO Menu (MenuID, ParentID, MenuName)

VALUES (1, NULL, 'File'),

       (2, 1, 'New'),

       (3, 1, 'Open'),

       (4, 3, 'Project'),

       (5, 3, 'File'),

       (6, 1, 'Save'),

       (7, 1, 'Close'),

       (8, NULL, 'Edit'),

       (9, 8, 'Undo'),

       (10, 8, 'Redo');

The data in the table and the final result are as follows:-

The logic here is to select the top layer of the menu first and select corresponding menu items as inline list items. The code is as follows:-

SELECT l1.MenuName li,

       (SELECT L2.MenuName li,

              (SELECT L3.MenuName li

              FROM Menu L3

              WHERE L3.ParentID = L2.MenuID

              FOR XML PATH (''), ROOT ('ul'), TYPE) li

       FROM Menu L2

       WHERE L2.ParentID = l1.MenuID

       FOR XML PATH (''), ROOT ('ul'), TYPE) li

FROM Menu l1

WHERE l1.ParentID IS NULL

FOR XML PATH ('ul');

The result is:-

<ul>

       <li>

              File<ul>

                     <li>New</li>

                     <li>

                           Open<ul>

                                  <li>Project</li>

                                  <li>File</li>

                           </ul>

                     </li>

                     <li>Save</li>

                     <li>Close</li>

              </ul>

       </li>

</ul>

<ul>

       <li>

              Edit<ul>

                     <li>Undo</li>

                     <li>Redo</li>

              </ul>

       </li>

</ul>

Note:-

This may not be the best solution if the number of possible levels is unknown.

Hello World !!!

Hello World…!! :-) Welcome to my blog……  I’m Chamika Goonetilaka, a software developer who is interested in C#, SQL Server and JavaScript. I’m mainly into Web Development, Data Visualization, Business Intelligence, Data Mining and Pattern Recognition. So await for my posts…….!!!