Inserting many rows in treelike structure in SQL SERVER 2005

Question

I have a few tables in SQL that are pretty much like this

A            B           C
ID_A         ID_B        ID_C
Name         Name        Name
             ID_A        ID_B

As you can see, A is linked to B and B to C. Those are basically tables that contains data models. Now, I would need to be able to create date based on those tables. For example, if I have the following datas

A                    B                        C
1    Name1           1  SubName1   1          1  SubSubName1  1
2    Name2           2  SubName2   1          2  SubSubName2  1
                     3  SubName3   2          3  SubSubName3  2
                                              4  SubSubName4  3
                                              5  SubSubName5  3

I would like to copy the 'content' of those tables in others tables. Of course, the auto numeric key that is generated when inserting into the new tables are diffirent that those one and I would like to be able to keep track so that I can copy the entire thing. The structure of the recipient table contains more information that those, but it's mainly dates and other stuff that are easy to get for me.

I would need to this entirely in TRANSACT-SQL (with built-in function if needed). Is this possible and can anyone give me a short example. I manage to do it for one level, but I get confused for the rest.

thanks

EDIT : The info above is just an example, because my actual diagram looks more like this

Model tables :

Processes -- (1-N) Steps -- (1-N) Task             -- (0-N) TaskCheckList
                         -- (0-N) StepsCheckLists

Where as the table I need to fill looks like this

Client -- (0-N) Sequence -- (1-N) ClientProcesses -- (1-N) ClientSteps -- (1-N)ClientTasks -- (0-N) ClientTaskCheckList
                                                                       -- (0-N)ClientStepCheckLists

The Client already exists and when I need to run the script, I create one sequence, which will contains all processes, which will contains its steps, taks, etc...

You can insert your own identity values by surrounding your `INSERT` statements with `SET IDENTITY_INSERT [TableName] ON` and `SET IDENTITY_INSERT [TableName] OFF`. Also, this structure could be simplified to a single table with self-referencing foreign keys, and the data can be selected recursively using self-joins. — Cᴏʀʏ, Apr 25 '12 at 18:48
Hi, This may seems pretty simeple, but this is because it's a example. The actual diagrams has 6 tables. Plus I can't use my own identity because I want to be able to create multiple copies. I will edit my post with more info. — David Brunelle, Apr 25 '12 at 18:53
Here's an interesting solution to the same problem, but it only works on SQL Server 2008 or above: http://stackoverflow.com/questions/5365629 — Chad, Apr 25 '12 at 19:04

score 0 · Accepted Answer · answered Apr 25 '12 at 20:37

Ok,

So I did a lot of trials and error, and here is what I got. It seems to work fine although it sound quite big for something that seemed easy at first.

The whole this is somehow in french and english because our client is french and so are we anyway. It does insert every date in all tables that I needed. The only thing left to this will be the first lines where I need to select the date to insert according to some parameters but this is the easy part.

DECLARE @IdProcessusRenouvellement bigint
DECLARE @NomProcessus nvarchar(255)

SELECT @IdProcessusRenouvellement = ID FROM T_Ref_Processus WHERE Nom LIKE 'EXP%'
SELECT @NomProcessus = Nom FROM T_Ref_Processus WHERE Nom LIKE 'EXP%'

DECLARE @InsertedSequence table(ID bigint)

DECLARE @Contrats table(ID bigint,IdClient bigint,NumeroContrat nvarchar(255))

INSERT INTO @Contrats SELECT ID,IdClient,NumeroContrat FROM T_ClientContrat

DECLARE @InsertedIdsSeq as Table(ID bigint)

-- Séquences de travail
INSERT INTO T_ClientContratSequenceTravail(IdClientContrat,Nom,DateDebut)
    OUTPUT Inserted.ID INTO @InsertedIdsSeq
    SELECT ID, @NomProcessus + ' - ' + CONVERT(VARCHAR(10), GETDATE(), 120) + ' : ' +     NumeroContrat ,GETDATE() 
    FROM @Contrats

-- Processus
DECLARE @InsertedIdsPro as Table(ID bigint,IdProcessus bigint)
INSERT INTO T_ClientContratProcessus
        (IdClientContratSequenceTravail,IdProcessus,Nom,DateDebut,DelaiRappel,DateRappel,LienAvecPro    cessusRenouvellement,IdStatutProcessus,IdResponsable,Sequence)
    OUTPUT Inserted.ID,Inserted.IdProcessus INTO @InsertedIdsPro
    SELECT     I.ID,P.ID,P.Nom,GETDATE(),P.DelaiRappel,GETDATE(),P.LienAvecProcessusRenouvellement,0,0,0
        FROM @InsertedIdsSeq I, T_Ref_Processus P 
        WHERE P.ID = @IdProcessusRenouvellement

-- Étapes
DECLARE @InsertedIdsEt as table(ID bigint,IdProcessusEtape bigint)
INSERT INTO T_ClientContratProcessusEtape 
        (IdClientContratProcessus,IdProcessusEtape,Nom,DateDebut,DelaiRappel,DateRappel,NomListeVeri    fication,Sequence,IdStatutEtape,IdResponsable,IdTypeResponsable,ListeVerificationTermine)
    OUTPUT Inserted.ID,Inserted.IdProcessusEtape INTO @InsertedIdsEt
SELECT I.ID,E.ID,
            E.Nom,GETDATE(),E.DelaiRappel,GETDATE(),COALESCE(L.Nom,''),E.Sequence,0,0,E.IdTypeResponsabl    e,0
FROM @InsertedIdsPro I INNER JOIN  T_Ref_ProcessusEtape E ON I.IdProcessus =     E.IdProcessus
LEFT JOIN T_Ref_ListeVerification L ON E.IdListeVerification = L.ID

-- Étapes : Items de la liste de vérification   
INSERT INTO T_ClientContratProcessusEtapeListeVerificationItem
    (IdClientContratProcessusEtape,Nom,Requis,Verifie)  
    SELECT I.ID,IT.Nom,IT.Requis,0
    FROM @InsertedIdsEt I 
    INNER JOIN T_Ref_ProcessusEtape E ON I.IdProcessusEtape = E.ID
    INNER JOIN T_Ref_ListeVerificationItem IT ON E.IdListeVerification =     IT.IdListeVerification

-- Tâches
DECLARE @InsertedIdsTa as table(ID bigint, IdProcessusEtapeTache bigint)
INSERT INTO T_ClientContratProcessusEtapeTache
        (IdClientContratProcessusEtape,IdProcessusEtapeTache,Nom,DateDebut,DelaiRappel,DateRappel,No    mListeVerification,Sequence,IdStatutTache,IdResponsable,IdTypeResponsable,ListeVerificationT    ermine)
    OUTPUT Inserted.ID,Inserted.IdProcessusEtapeTache INTO @InsertedIdsTa
    SELECT I.ID,T.ID,
               T.Nom,GETDATE(),T.DelaiRappel,GETDATE(),COALESCE(L.Nom,''),T.Sequence,0,0,T.IdTypeResponsabl    e,0
    FROM @InsertedIdsEt I 
    INNER JOIN T_Ref_ProcessusEtapeTache T ON I.IdProcessusEtape = T.IdProcessusEtape
    LEFT JOIN T_Ref_ListeVerification L ON T.IdListeVerification = L.ID


-- Tâches : Items de la liste de vérification
INSERT INTO T_ClientContratProcessusEtapeTacheListeVerificationItem
    (IdClientContratProcessusEtapeTache,Nom,Requis,Verifie) 
    SELECT I.ID,IT.Nom,IT.Requis,0
    FROM @InsertedIdsTa I 
    INNER JOIN T_Ref_ProcessusEtapeTache T ON I.IdProcessusEtapeTache = T.ID
    INNER JOIN T_Ref_ListeVerificationItem IT ON T.IdListeVerification =     IT.IdListeVerification

Inserting many rows in treelike structure in SQL SERVER 2005

1 Answers1