Быстрый способ заменить имена на идентификаторы в Datatable?

Question

У меня есть очень большой файл CSV, который я должен загружать на регулярной основе, который содержит данные временных рядов. Примеры заголовков ниже:

| SiteName | Company | Date | ResponseTime | Clicks |

Эти данные поступают от службы, внешней по отношению к загрузчику. SiteName а также Company оба строковых поля. В базе данных они нормализованы. E сть Site стол и Company Таблица:

CREATE TABLE [dbo].[Site] (
    [Id] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    [Name] NVARCHAR(MAX) NOT NULL
)

CREATE TABLE [dbo].[Company] (
    [Id] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    [Name] NVARCHAR(MAX) NOT NULL
)

Как и данные таблицы.

CREATE TABLE [dbo].[SiteStatistics] (
    [Id] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    [CompanyId] INT NOT NULL,
    [SiteId] INT NOT NULL,
    [DataTime] DATETIME NOT NULL,
    CONSTRAINT [SiteStatisticsToSite_FK] FOREIGN KEY ([SiteId]) REFERENCES [Site]([Id]),
    CONSTRAINT [SiteStatisticsToCompany_FK] FOREIGN KEY ([CompanyId]) REFERENCES [Company]([Id])
)

Приблизительно в 2 миллионах строк в файле CSV любая итерация, связанная с вводом-выводом, не будет работать. Мне нужно сделать это за несколько минут, а не дней.

Моя первоначальная мысль, что я мог бы предварительно загрузить Site а также Company в DataTables. Я уже загрузил CSV в таблицу данных в формате, соответствующем столбцам CSV. Мне нужно теперь заменить каждый SiteName с полем Id Site и каждый Company с полем Id Company, Какой самый быстрый и эффективный способ справиться с этим?

Answer 1

Если вы выберете "Предварительная загрузка сайтов и компаний", вы можете получить различные значения с помощью кода:

DataView view = new DataView(table);
DataTable distinctCompanyValues = view.ToTable(true, "Company")

DataView view = new DataView(table);
DataTable distinctSiteValues = view.ToTable(true, "Site")

Затем загрузите эти две таблицы данных в их таблицы SQL, используя Sql-Bulk-Copy.

---

Далее выведите все данные в:

CREATE TABLE [dbo].[SiteStatistics] (
    [Id] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    [CompanyId] INT DEFAULT  0,
    [SiteId] INT DEFAULT 0,
    [Company] NVARCHAR(MAX) NOT NULL,
    [Site] NVARCHAR(MAX) NOT NULL,
    [DataTime] DATETIME NOT NULL
)

---

Затем выполните ОБНОВЛЕНИЕ, чтобы установить поля ссылочной целостности:

UPDATE [SiteStatistics] ss SET
[CompanyId] = (SELECT Id FROM [Company] c Where ss.[Company] = c.Name),
[SiteId] = (SELECT Id FROM [Site] s Where ss.[Site] = s.Name)

Добавьте ограничения внешнего ключа:

ALTER TABLE [SiteStatistics] ADD CONSTRAINT [SiteStatisticsToSite_FK] FOREIGN KEY ([SiteId]) REFERENCES [Site]([Id])
ALTER TABLE [SiteStatistics] ADD CONSTRAINT [SiteStatisticsToCompany_FK] FOREIGN KEY ([CompanyId]) REFERENCES [Company]([Id])

---

В конце удалите поля Site & Company name из SiteStatistics:

ALTER TABLE [SiteStatistics] DROP COLUMN [Company];
ALTER TABLE [SiteStatistics] DROP COLUMN [Site];