I have an Excel sheet that is populated by HR employee with thousands of client records it looks like this one:
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
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
But it's not right to force the end user to put some foreign keys in the Excel sheet before import.
Sorry for uploading screenshots to Tinypic but I couldn't upload them here because of my Rep Points.
Thanks in advance
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.