Importing user-friendly Excel sheet into a SQL Server table that has many foreign keys

c# excel sqlbulkcopy sqldataadapter sql-server

Question

I have an Excel sheet that is populated by HR employee with thousands of client records it looks like this one:

User Friendly Excel Sheet Example Screenshot

My client's SQL Server table schema looks like this

CREATE TABLE [dbo].[Clients] (
[ID]            INT            IDENTITY (1, 1) NOT NULL,
[Name]          NVARCHAR (100) NOT NULL,
[Photo]         VARCHAR (200)  NOT NULL,
[PolicyID]      INT            NOT NULL,
[BirthDay]      DATE           NOT NULL,
[Gender]        BIT            NOT NULL,
[Title]         NVARCHAR (100) NULL,
[Nationality]   NVARCHAR (100) NOT NULL,
[Relationship]  NVARCHAR (50)  NOT NULL,
[ClassID]       INT            NOT NULL,
[SponsorID]     INT            NULL,
[HRID]          INT            NOT NULL,
[Active]        BIT            CONSTRAINT [DF_Clients_Active] DEFAULT ((1)) NOT NULL,
[StartingDate]  DATE           NOT NULL,
[EndingDate]    DATE           NOT NULL,
[AddingDate]    DATETIME       NOT NULL,
[Creator]       INT            NOT NULL,
[UniqueID]      NVARCHAR (50)  NULL,
[PassportNo]    NVARCHAR (50)  NULL,
CONSTRAINT [PK_Clients] PRIMARY KEY CLUSTERED ([ID] ASC),
CONSTRAINT [FK_Clients_Clients] FOREIGN KEY ([SponsorID]) REFERENCES [dbo].[Clients] ([ID]),
CONSTRAINT [FK_Clients_Employees] FOREIGN KEY ([HRID]) REFERENCES [dbo].[Employees] ([ID]),
CONSTRAINT [FK_Clients_Employees1] FOREIGN KEY ([Creator]) REFERENCES [dbo].[Employees] ([ID]),
CONSTRAINT [FK_Clients_Policy] FOREIGN KEY ([PolicyID]) REFERENCES [dbo].[Policy] ([ID]),
CONSTRAINT [FK_Clients_Classes] FOREIGN KEY ([ClassID]) REFERENCES [dbo].[Classes] ([ID])
);

What is the best approach to achieve such inserts?

I've tried using SqlBulkCopy but it doesn't allow any manipulation on the inserted rows.

I've tried also using SqlAdapter.Update(Datatable) but it failed since I've read the Excel sheet using ExcelDataReader then tried to add some columns like Creator and Adding Date at runtime and when I tried to run Adapter.Update(ModifiedDatatable) it throws an exception

Update requires a valid UpdateCommand when passed DataRow collection with modified rows

When I tried to use SqlBulkCopy to insert this Excel sheet it worked as expected

Excel Sheet with Foreign Keys Screenshot

But it's not right to force the end user to put some foreign keys in the Excel sheet before import.

Notice:

Sorry for uploading screenshots to Tinypic but I couldn't upload them here because of my Rep Points.

Thanks in advance

1
1
6/6/2015 2:44:36 AM

Accepted Answer

I would be creating an SSIS package in this scenario. SSIS can read from Excel, and you can get it to query the database for the extra information to build a valid dataset that will not violate the FK constraints.

1
6/6/2015 2:37:05 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow