时间戳

首页 > 解决方案 > 用数据表中的 ID 替换名称的快速方法？

c# - 用数据表中的 ID 替换名称的快速方法？

问题描述

我有一个非常大的 CSV 文件，我必须定期加载其中包含时间序列数据。标题示例如下：

| SiteName | Company | Date | ResponseTime | Clicks |

此数据来自上传者外部的服务。SiteName并且Company都是字符串字段。在数据库中，这些是标准化的。有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])
)

在 CSV 文件中大约有 200 万行的情况下，任何类型的 IO 绑定迭代都不起作用。我需要在几分钟内完成，而不是几天。

我最初的想法是我可以预加载Site并Company进入 DataTables。我已经以与 CSV 列匹配的格式将 CSV 加载到数据表中。我现在需要将 every 替换SiteName为 的 Id 字段，Site并将 everyCompany替换为 的 Id 字段Company。处理这个问题的最快、最有效的方法是什么？

标签： c#sqlsql-serversqlbulkcopyimport-csv

解决方案

如果您使用预加载站点和公司，您可以使用代码获得不同的值：

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

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

然后使用 Sql-Bulk-Copy 将这两个 DataTables 加载到它们的 SQL 表中。

接下来转储所有数据：

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 设置参照完整性字段：

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

最后从 SiteStatistics 中删除站点和公司名称字段：

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

推荐阅读