将用户友好的Excel表导入具有许多外键的SQL Server表

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

我有一个Excel表格，里面有人力资源部的员工，里面有成千上万的客户记录，看起来是这样的:

User Friendly Excel Sheet Example Screenshot

用户友好的Excel表格示例截图

My client's SQL Server table schema looks like this

我的客户机的SQL Server表模式是这样的。

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.

我尝试过使用SqlBulkCopy，但是它不允许对插入行进行任何操作。

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

我也尝试过使用SqlAdapter.Update(Datatable)，但失败了，因为我已经使用ExcelDataReader读取了Excel表，然后尝试在运行时添加Creator和Date等列，当我尝试运行Adapter.Update(ModifiedDatatable)时，它会抛出异常

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

当通过带有修改行的DataRow集合时，更新需要一个有效的UpdateCommand

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

当我尝试使用SqlBulkCopy插入这个Excel表时，它像预期的那样工作

Excel Sheet with Foreign Keys Screenshot

带有外键的Excel表格截图

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

但是，强制最终用户在导入之前在Excel表中放入一些外键是不对的。

Notice:

注意:

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

很抱歉把截图上传到Tinypic，但是因为我的积分，我无法上传。

Thanks in advance

谢谢提前

1 个解决方案

#1

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.

我将在这个场景中创建一个SSIS包。SSIS可以从Excel中读取数据，您可以让它查询数据库中的额外信息，以构建一个不违反FK约束的有效数据集。