Insert SQL en bloc avec relations parent / enfant, l'ordre est-il préservé?

c# sqlbulkcopy sql-server-2008

Question

Semblable à ces autres questions notées ci-dessous, j'ai deux tableaux avec la structure:

create table parent (
   recno int identity(1,1) primary key not null,
   groupCode int,
   parentdata varchar(80)
);

create table child (
   parentrecno int not null,
   childdata varchar(80)
)

J'ai besoin d'insérer rapidement quelques centaines de milliers d'enregistrements dans ces tables. Ces tables contiennent des millions d'autres enregistrements sans lien avec cet insert et ne sont jamais silencieuses. En raison de la nature parent / enfant, ce n'est pas un bon candidat (semble-t-il) pour SqlBulkCopy .

En C # avec SqlCommand avec INSERT je reçois environ 400-500 enregistrements / seconde insérés, ce qui est un peu trop lent. Pseudocode:

 foreach(Record r in parentRecords)
 {
      Insert Fields from r into SqlCommand Parameters but not "recno"
      Call ExecuteScalar to insert and fetch the inserted identity value (recno)
      foreach(ChildRecord cr in parentRecords.Children)
      {
          Insert Fields from cr into SqlCommand Parameters
          Insert the identity value (recno) from above into Parameters 
                                                       (as parentrecno)
          Call ExecuteNonQuery to insert the record
      }   
 }

Après avoir lu ces autres articles, je me suis dit: Le groupCode attaché aux enregistrements parents est unique à cet ensemble d'enregistrements parents que j'insère. Cela fonctionnerait-il pour:

  1. En vrac, insérez les enregistrements parents avec SqlBulkCopy , ce qui permet à l'insert de générer automatiquement le recno identité recno comme d'habitude.
  2. Effectuez un SELECT sur les enregistrements insérés uniquement:

    select recno from parent where groupCode = @thisgroup order by recno;
    
  3. Utilisez les valeurs récupérées pour remplir les champs parentrecno pour les enregistrements enfants en mémoire

  4. En vrac insérer les enregistrements enfants avec SqlBulkCopy

Cela reposerait sur les enregistrements parent allant dans la table SQL dans le même ordre que dans le DataTable d'origine (et les valeurs d'identité étant assignées dans le même ordre). Est-ce une chose sur laquelle je peux compter?

Questions connexes:

Comment mettre à jour les tables parent et enfant du jeu de données avec une clé d'identité générée automatiquement?

SqlBulkCopy et DataTables avec relation parent / enfant sur la colonne d'identité

Réponse acceptée

Créez deux tables de transfert ayant la même structure que vos tables cible, mais n'utilisez pas l'identité sur la colonne recno.

create table parentTmp (
   recno int,
   groupCode int,
   parentdata varchar(80)
);

create table childTmp (
   parentrecno int not null,
   childdata varchar(80)
)

Chargez vos données en bloc dans les tables de transfert en conservant les valeurs recno / parentrecno telles quelles

Ensuite, vous pouvez utiliser la fusion et la sortie pour déplacer les données des tables de transfert.

-- Table variable to hold mapping between 
-- SourceRecno and TargetRecno
declare @recno table(SourceRecno int, TargetRecno int);

-- Merge data from parentTmp to parent
-- Output old and new recno to @recno
merge parent T
using parentTmp S
on 0=1
when not matched then
  insert (groupCode, parentdata)
    values (S.groupCode, S.parentData)
output S.recno, inserted.recno into @recno;

-- Copy data from childTmp to child
-- Use @recno to get the new recno
insert into child(parentrecno, childdata)
select R.TargetRecno, C.childdata
from childTmp as C
  inner join @recno as R
    on C.parentrecno = R.SourceRecno;

Cela ne fonctionnera que dans SQL Server 2008 (et plus tard, je présume).


Réponse populaire

Il ne s'agit pas d'une insertion en bloc absolue, mais elle insère toutes les données enfants en même temps avec les données parent, pour ne créer qu'un aller-retour vers la base de données.

insert into parent(groupcode, parentdata) values(1, 'parent data');
insert into child(parentrecno, childdata) select parentrecno, childdata from (
    select SCOPE_IDENTITY() as parentrecno, 'child data 1' as childdata
    union
    select SCOPE_IDENTITY() as parentrecno, 'child data 2' as childdata
    union
    select SCOPE_IDENTITY() as parentrecno, 'child data 3' as childdata
) childrendata;

Vous pouvez créer des scripts comme celui-ci dans votre code C #, puis effectuer une demande par parent.

Sachez que cela peut ne pas être une bonne approche si l’on sait que la quantité de données sur les enfants est importante. Je ne connais pas les détails, mais je suis sûr que la taille du script SQL ne peut pas augmenter indéfiniment.



Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi
Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi