SQL Bulk Insert с родительскими / дочерними отношениями, сохраняется ли порядок?

Подобно этим другим вопросам, указанным ниже, у меня есть две таблицы со структурой:

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)
)

Мне нужно быстро вставить несколько сотен тысяч записей в эти таблицы - а таблицы содержат миллионы других записей, не связанных с этой вставкой, и никогда не будут тихими. Из-за родительской / дочерней природы это не лучший кандидат (кажется) для SqlBulkCopy.

В C # с использованием SqlCommand с INSERT я получаю около 400-500 записей в секунду, и это слишком медленно. Псевдокод:

 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
      }   
 }

После прочтения других постов у меня возникла мысль. groupCode, прикрепленный к родительским записям, уникален для того набора родительских записей, который я вставляю. Будет ли работать:

  1. Массовая вставка родительских записей с SqlBulkCopy, позволяя вставке автоматически генерировать поле идентификатора recno, как обычно.

  2. Выполните SELECT только для вставленных записей:

    select recno from parent where groupCode = @thisgroup order by recno;

  3. Используйте полученные значения, чтобы заполнить parentrecno поля для дочерних записей в памяти.

  4. Массовая вставка дочерних записей с SqlBulkCopy

Это будет полагаться на родительские записи, входящие в таблицу SQL в том же порядке, что и в исходной таблице DataTable (и присвоение значений идентификаторов в том же порядке). Могу ли я положиться на это?

Связанные вопросы:

Как обновить родительские и дочерние таблицы набора данных с помощью Автоматически сгенерированный идентификационный ключ?

SqlBulkCopy и DataTables с отношениями родитель / потомок в столбце идентичности < / а>


c# sql-server-2008 sqlbulkcopy
person Clinton Pierce    schedule 22.06.2011    source источник

Ответы (2)


arrow_upward
9
arrow_downward

Создайте две промежуточные таблицы с той же структурой, что и ваши целевые таблицы, но не используйте идентификатор в столбце recno.

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

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

Массовая загрузка данных в промежуточные таблицы, сохраняя значения recno / parentrecno как есть.

Затем вы можете использовать слияние и output, чтобы переместить данные из промежуточных таблиц.

-- 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;

Это будет работать только в SQL Server 2008 (и я предполагаю, что позже).

person Mikael Eriksson    schedule 20.07.2011
comment
Это интересный фрагмент кода, использующий операторы, с которыми я не знаком. Хотя это звучит правдоподобно. :) Дай немного пощупать. - person Clinton Pierce; 22.07.2011
comment
@clintp Вы можете посмотреть этот вопрос для получения дополнительной информации. stackoverflow.com/questions/5365629/ - person Mikael Eriksson; 22.07.2011
comment
Есть ли способ сделать те же операторы в SQL 2005. Я попытался найти адаптацию с использованием слияния для 2005 года, но мне не удалось получить доступ ни к идентификатору исходной таблицы, ни к вставленному идентификатору в целевой таблице. И в дополнение к вашему утверждению: вам следует подумать об использовании option(recompile) во внутреннем соединении. mssqltips.com/sqlservertip/2140 / - person Danielku15; 24.07.2013

arrow_upward
1
arrow_downward

Это не абсолютная массовая вставка, но вместо этого она вставляет все дочерние данные одновременно с родительскими данными, делая только 1 круговой обход БД.

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;

Вы можете создавать подобные сценарии в своем коде C #, а затем выполнять один запрос для каждого родителя.

Имейте в виду, что это не может быть хорошим подходом, если известно, что объем дочерних данных велик. Не знаю подробностей, но уверен, что размер sql-скрипта не может расти бесконечно.

person Fede    schedule 20.07.2011