Transposing hierachical data from one VARCHAR to two INTs

Question

THE PROBLEM

The table where the data is recorded is

CREATE TABLE [dbo].[Almoxarifado](
    [idAlmoxarifado] [varchar](20) NOT NULL,
    [tipoAlmoxarifadoId] [varchar](30) NOT NULL,
    [entidadeId] [bigint] NOT NULL,
    [dtInclusao] [smalldatetime] NOT NULL,
    [dtUltimaAlteracao] [smalldatetime] NULL,
    [descricao] [varchar](255) NOT NULL,
    [terceiro] [bit] NOT NULL
) ON [PRIMARY]

And this is a SELECT TOP 10 * example (the table has more than 1 million entries):

As you can see, the idAlmoxarifadofield is storing a hierarchical data that has no hierarchical relationship as most hierarchical table have.

Now I want to bring this data to a usual hierarchical table:

CREATE TABLE [dbo].[Almoxarifado2](
    [idMaster] [bigint] IDENTITY(1,1) NOT NULL,
    [idAlmoxarifado] [int] NOT NULL,
    [idAlmoxPai] [int] NOT NULL DEFAULT ((0)),
    [entidadeId] [bigint] NOT NULL,
    [tipoAlmoxarifadoId] [varchar](30) NOT NULL 
    [dtInclusao] [datetime] NULL DEFAULT (getdate()),
    [dtUltimaAlteracao] [datetime] NULL,
    [descricao] [varchar](255) NOT NULL,
    [terceiro] [bit] NULL DEFAULT ((0)),
    PRIMARY KEY ([idMaster])
)

On this table I'll have a usual father-son relationship where the "father" of a given field will always be it's idMastercorrespondent (unless it is on the first degree of the hierarchical chain; then it's "father" would be 0).

So, if we follow the same order shown on the image above, we should have (after inserting the transposed data on the new table) :

THE QUESTION

How can I do this transposition is a safe and painless way since the original table has more than 1 mi entries?

THE RULES

There are no restrictions on how many top-levels could exist; neither children levels. Our idea is to work with a mask to make the data useful on our application (we workd with Motorola Handheld Computer; check MC45 and MC3190). Let's say that the customer that buys our solution has several Warehouses where he stocks his products. In this case he'll be able to define a mask (say 9.99.99.999 or 9.9.99.99.99.999) and our system will have to be able to use this mask to allow him to register his warehouses in those levels.

So you can basically say that our system is foccused on logistics in a fashion.

Where the 1st level could be "NY South Warehouse", the 2nd "Room #2", 3rd "Corridor A", 4th "Locker 2" and 5th "Shelf 3".

Answer 1

As your data looks a bit like hierarchyId I thought about using that. Initial versions did not scale well up to 1 million rows, however a few indexing choices on the main temp table have helped. However issues could also be due to my test data, so can you tell me a bit more about your hierarchy? For example how many top-level parents are there, how many levels are there on average, how many children might each level have?

In the meantime, here is a version so you can have a look through it and see if hierarchyId could work for you.

The basic idea is to create a temp table with the original ids converted into hierarchyIds, then walk the hierarchy to work out the lineage. This temp table (which could be permanent) could then be used for the migration:

USE tempdb
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('[dbo].[Almoxarifado]') IS NOT NULL DROP TABLE [dbo].[Almoxarifado]
IF OBJECT_ID('[dbo].[Almoxarifado2]') IS NOT NULL DROP TABLE [dbo].[Almoxarifado2]
GO


CREATE TABLE [dbo].[Almoxarifado](
    [idAlmoxarifado] [varchar](20) NOT NULL,
    [tipoAlmoxarifadoId] [varchar](30) NOT NULL,
    [entidadeId] [bigint] NOT NULL,
    [dtInclusao] [smalldatetime] NOT NULL,
    [dtUltimaAlteracao] [smalldatetime] NULL,
    [descricao] [varchar](255) NOT NULL,
    [terceiro] [bit] NOT NULL
) ON [PRIMARY]
GO



CREATE TABLE [dbo].[Almoxarifado2](
    [idMaster] [bigint] IDENTITY(1,1) NOT NULL,
    [idAlmoxarifado] [int] NOT NULL,
    [idAlmoxPai] [int] NOT NULL DEFAULT ((0)),
    [entidadeId] [bigint] NOT NULL,
    [tipoAlmoxarifadoId] [varchar](30) NOT NULL ,
    [dtInclusao] [datetime] NULL DEFAULT (getdate()),
    [dtUltimaAlteracao] [datetime] NULL,
    [descricao] [varchar](255) NOT NULL,
    [terceiro] [bit] NULL DEFAULT ((0)),
    PRIMARY KEY ([idMaster])
)
GO

/*
-- Test data
INSERT INTO [dbo].[Almoxarifado] ( idAlmoxarifado, tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro )
VALUES
    ( '1', 'TESTE', 12, '29 Apr 2016', NULL, '0000000', 0 ),
    ( '1.1', 'TESTE', 12, '29 Apr 2016', NULL, '0000001', 0 ),
    ( '1.1.01', 'TESTE', 12, '29 Apr 2016', NULL, '0000002', 0 ),
    ( '1.1.01.01', 'TESTE', 12, '29 Apr 2016', NULL, '0000003', 0 ),
    ( '1.1.01.01.001', 'TESTE', 12, '29 Apr 2016', NULL, '0000004', 0 ),

    ( '1.1.01.01.002', 'TESTE', 12, '29 Apr 2016', NULL, '0000005', 0 ),
    ( '1.1.01.01.003', 'TESTE', 12, '29 Apr 2016', NULL, '0000006', 0 ),
    ( '1.1.01.01.004', 'TESTE', 12, '29 Apr 2016', NULL, '0000007', 0 ),
    ( '1.1.01.01.005', 'TESTE', 12, '29 Apr 2016', NULL, '0000008', 0 ),
    ( '1.1.01.01.006', 'TESTE', 12, '29 Apr 2016', NULL, '0000009', 0 )
GO
*/

-- Add 10 parent levels
INSERT INTO [dbo].[Almoxarifado] ( idAlmoxarifado, tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro )
VALUES
    ( '1', 'TESTE', 1, '1 Jan 2016', NULL, '00000001', 0 ),
    ( '2', 'TESTE', 1, '1 Feb 2016', NULL, '00000002', 0 ),
    ( '3', 'TESTE', 1, '1 Mar 2016', NULL, '00000003', 0 ),
    ( '4', 'TESTE', 1, '1 Apr 2016', NULL, '00000004', 0 ),
    ( '5', 'TESTE', 1, '1 May 2016', NULL, '00000005', 0 ),
    ( '6', 'TESTE', 1, '1 Jun 2016', NULL, '00000006', 0 ),
    ( '7', 'TESTE', 1, '1 Jul 2016', NULL, '00000007', 0 ),
    ( '8', 'TESTE', 1, '1 Aug 2016', NULL, '00000008', 0 ),
    ( '9', 'TESTE', 1, '1 Sep 2016', NULL, '00000008', 0 ),
    ( '10', 'TESTE', 1, '1 Oct 2016', NULL, '00000010', 0 )


-- For each parent, add 3 sublevels
INSERT INTO [dbo].[Almoxarifado] ( idAlmoxarifado, tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro )
SELECT 
    idAlmoxarifado + '.' + CAST( x.y AS VARCHAR(10) ),
    tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro
FROM [dbo].[Almoxarifado] a
    CROSS JOIN ( SELECT TOP 5 idAlmoxarifado y FROM [dbo].[Almoxarifado] ) x


-- add n sublevels
INSERT INTO [dbo].[Almoxarifado] ( idAlmoxarifado, tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro )
SELECT 
    idAlmoxarifado + '.0' + CAST( x.y AS VARCHAR(10) ),
    tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro
FROM [dbo].[Almoxarifado] a
    CROSS JOIN ( SELECT TOP 8 idAlmoxarifado y FROM [dbo].[Almoxarifado] WHERE idAlmoxarifado Not Like '%.%' ) x
WHERE idAlmoxarifado Like '%.%'


-- Add 8 sublevels
INSERT INTO [dbo].[Almoxarifado] ( idAlmoxarifado, tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro )
SELECT 
    idAlmoxarifado + '.0' + CAST( x.y AS VARCHAR(10) ),
    tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro
FROM [dbo].[Almoxarifado] a
    CROSS JOIN ( SELECT TOP 13 ROW_NUMBER() OVER( ORDER BY idAlmoxarifado ) y FROM [dbo].[Almoxarifado] ) x
WHERE idAlmoxarifado Like '%.%.%'


-- Add 9 sublevels
INSERT INTO [dbo].[Almoxarifado] ( idAlmoxarifado, tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro )
SELECT 
    idAlmoxarifado + '.0' + CAST( x.y AS VARCHAR(10) ),
    tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro
FROM [dbo].[Almoxarifado] a
    CROSS JOIN ( SELECT TOP 21 ROW_NUMBER() OVER( ORDER BY idAlmoxarifado ) y FROM [dbo].[Almoxarifado] ) x
WHERE idAlmoxarifado Like '%.%.%.%'
GO

INSERT INTO [dbo].[Almoxarifado] ( idAlmoxarifado, tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro )
SELECT 
    idAlmoxarifado + '.0' + CAST( x.y AS VARCHAR(10) ),
    tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro
FROM [dbo].[Almoxarifado] a
    CROSS JOIN ( SELECT TOP 9 ROW_NUMBER() OVER( ORDER BY idAlmoxarifado ) y FROM [dbo].[Almoxarifado] ) x
WHERE idAlmoxarifado Like '%.%.%.%.%'
GO



-- Main hierarchyId processing
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

SELECT
    IDENTITY( INT, 1, 1 ) AS idMaster,
    idAlmoxarifado,
    CAST( '/' + REPLACE( REPLACE( REPLACE( idAlmoxarifado, '.0', '.' ), '.0', '.' ), '.', '/' ) + '/' AS hierarchyid ) hId,
    CAST( '/' + REPLACE( REPLACE( REPLACE( idAlmoxarifado, '.0', '.' ), '.0', '.' ), '.', '/' ) + '/' AS hierarchyid ).ToString() hString,
    CAST( NULL AS INT ) AS idAlmoxarifado2,
    CAST( NULL AS INT ) AS idAlmoxPai

INTO #tmp
FROM [dbo].[Almoxarifado]
GO


-- Index temp table to help with recursive CTE
ALTER TABLE #tmp ADD PRIMARY KEY ( idMaster )
ALTER TABLE #tmp ALTER COLUMN hId hierarchyId NOT NULL
CREATE UNIQUE INDEX _idx ON #tmp ( hId ) INCLUDE ( idAlmoxarifado, hString )
GO


-- Walk the hierarchy
;WITH cte AS (
SELECT 1 AS xlevel, idMaster, idAlmoxarifado, hId, hString, hId.GetLevel() getLevel, 0 AS parentId
FROM #tmp
WHERE hId.GetLevel() = 1

UNION ALL

SELECT xlevel + 1, t.idMaster, t.idAlmoxarifado, t.hId, t.hString, t.hId.GetLevel() getLevel, c.idMaster AS parentId
FROM cte c
    INNER JOIN #tmp t ON c.hId  = t.hId.GetAncestor(1)
)
UPDATE t
SET 
    -- Parse out the individual node, by taking its parent and stuffing it in front
    t.idAlmoxarifado2 = CAST( REPLACE( STUFF( c.hString, 1, LEN(c.hId.GetAncestor(1).ToString()), '' ), '/', '' ) AS INT ),
    t.idAlmoxPai = parentId
FROM cte c
    INNER JOIN #tmp t ON c.idMaster = T.idMaster


-- Check results
SELECT *
FROM #tmp

This whole script (including test data creation) runs in only a few minutes on my laptop.

Answer 2

Add a column to dbo.Almoxarifado2 that will hold the original value of idAlmoxarifado.

Add all rows from dbo.Almoxarifado and then use idAlmoxarifadoOrg to update idAlmoxPai with the value of idMaster for the parent row.

Code with some comments:

-- Add a column to store original idAlmoxarifado
alter table dbo.Almoxarifado2 
    add idAlmoxarifadoOrg varchar(20) not null;

go

-- Add all rows to Almoxarifado2 with 0 to idAlmoxPai 
-- and the rightmost number of idAlmoxarifado to idAlmoxarifado.
-- Original value of idAlmoxarifado goes to idAlmoxarifadoOrg
insert into dbo.Almoxarifado2(idAlmoxarifado, idAlmoxPai, entidadeId, tipoAlmoxarifadoId, dtInclusao, dtUltimaAlteracao, descricao, terceiro, idAlmoxarifadoOrg)
select right(A.idAlmoxarifado, charindex('.', reverse('.'+A.idAlmoxarifado)) - 1), 0, A.entidadeId, A.tipoAlmoxarifadoId, A.dtInclusao, A.dtUltimaAlteracao, A.descricao, A.terceiro, A.idAlmoxarifado
from dbo.Almoxarifado as A;

go

-- Update idAlmoxPai with the parent idMaster
-- Don't update root nodes
update A
set idAlmoxPai = P.idMaster
from dbo.Almoxarifado2 as A
  inner join dbo.Almoxarifado2 as P
    on P.idAlmoxarifadoOrg = left(A.idAlmoxarifadoOrg, len(A.idAlmoxarifadoOrg) - charindex('.', reverse(A.idAlmoxarifadoOrg)))
where charindex('.', A.idAlmoxarifadoOrg) > 0;

go

-- Cleanup
alter table dbo.Almoxarifado2 drop column idAlmoxarifadoOrg;

Some parts could do with some more information.

This right(A.idAlmoxarifado, charindex('.', reverse('.'+A.idAlmoxarifado)) - 1) extracts the last number in idAlmoxarifado. So 1.1.10 will give you a 10.

And this part left(A.idAlmoxarifado, len(A.idAlmoxarifado) - charindex('.', reverse(A.idAlmoxarifado))) returns all but the last number. For 1.1.10 it will return 1.1.

I shamelessly stole the test data generated by wBob (thanks and much appreciated) and found out that this version is faster on my machine. I use a really ancient 2 core laptop for the tests so the result could be different on a real server. Any way, for me it took 3 minutes to execute the code by wBob and my solution was about 20 seconds.