Agfdhyk

I have a very large CSV file I have to load on a regular basis that contains time series data. Examples of the headers are below:

| SiteName | Company | Date | ResponseTime | Clicks |

This data comes from a service external to the uploader. SiteName and Company are both string fields. In the database these are normalized. There is a Site table and a Company table:

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

)

As well as the data table.

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

)

At around 2 million rows in the CSV file any sort of IO-bound iteration isn't going to work. I need this done in minutes, not days.

My initial thought is that I could pre-load Site and Company into DataTables. I already have the CSV loaded into a datatable in the format that matches the CSV columns. I need to now replace every SiteName with the Id field of Site and every Company with the Id field of Company. What is the quickest, most efficient way to handle this?

If you go with Pre-Loading the Sites and Company's you can get the distinct values using code:

DataView view = new DataView(table);

DataTable distinctCompanyValues = view.ToTable(true, "Company")



DataView view = new DataView(table);

DataTable distinctSiteValues = view.ToTable(true, "Site")

Then load those two DataTables into their SQL Tables using Sql-Bulk-Copy.

Next dump all the data in:

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

)

Then do an UPDATE to set the Referential Integrity fields:

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)

Add the Foreign Key constraints:

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

ALTER TABLE [SiteStatistics] DROP COLUMN [Company];

ALTER TABLE [SiteStatistics] DROP COLUMN [Site];