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

c# excel sqlbulkcopy sqldataadapter sql-server

Question

A HR staffer filled up an Excel page for me with hundreds of customer records, and it looks like this:

Screenshot of a user-friendly Excel spreadsheet

The SQL Server table structure for my customer 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 most effective method for achieving such inserts?

I've attempted to useSqlBulkCopy However, it forbids any alteration of the entered rows.

I also tried usingSqlAdapter.Update(Datatable) however it didn't work since I was reading the Excel document usingExcelDataReader then attempted to include certain columns, such asCreator and Adding Date as well as when I attempted to run itAdapter.Update(ModifiedDatatable) It raises an error.

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

When attempting to utilizeSqlBulkCopy This Excel sheet was successfully inserted as intended.

Image of an Excel Spreadsheet with Foreign Keys

However, forcing the user to add certain foreign keys to the Excel sheet before import is wrong.

Notice:

I'm sorry for using Tinypic, but my Rep Points prevented me from submitting screenshots here.

I appreciate you.

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

Accepted Answer

In this case, I would be developing an SSIS package. SSIS may receive data from Excel and be instructed to query a database for additional data in order to create a valid dataset that complies with FK requirements.

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