Transposing hierachical data from one VARCHAR to two INTs

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

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 NOT NULL,
[tipoAlmoxarifadoId] varchar NOT NULL,
[entidadeId] [bigint] NOT NULL,
[dtInclusao] [smalldatetime] NOT NULL,
[dtUltimaAlteracao] [smalldatetime] NULL,
[descricao] varchar NOT NULL,
[terceiro] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].Almoxarifado2 NOT NULL,
[idAlmoxarifado] [int] NOT NULL,
[idAlmoxPai] [int] NOT NULL DEFAULT ((0)),
[entidadeId] [bigint] NOT NULL,
[tipoAlmoxarifadoId] varchar NOT NULL ,
[dtInclusao] [datetime] NULL DEFAULT (getdate()),
[dtUltimaAlteracao] [datetime] NULL,
[descricao] varchar 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(

StackExchange Database Administrators Q#137565, answer score: 10