Returning PK's to C# using Table-Valued Parameters

Question

I have a large amount of data that I would like to send from a C# web application to a SQL Server database. At present I iterate over the data in loops and call stored procedures to add the data. I return the PK of the inserted (or existing) Client record and add this to the foreign key of the associated activity records and then add them. I'm aware that this is not very efficient and have since been investigating the use of table-valued parameters.

My dataset is similar to the following:

Client (ID, FirstName, Surname, DOB ...)
Activity1 (ID, ClientID(FK), Date ...)
Activity2 (ID, ClientID(FK), Date ...)

I would like to add all clients as a bulk insert and return the ID's using the OUTPUT clause in SQL Server and then add their associated activity with these ID's as the PK-FK relationship. The only thing I cant figure out is how to check if a client already exists (based on FirstName, Surname and DOB being the same) in the database and if so return that ID instead of adding the client in as a new record.

As a test, I have created a stored procedure that accepts a TVP like so:

CREATE PROCEDURE [dbo].[TestBulkAdd]
@Data AS dbo.Data READONLY
AS
BEGIN
    DECLARE @IDS TABLE (ID INT, FirstName NVARCHAR(50), Surname NVARCHAR(50), DOB DATETIME2)
    INSERT INTO Clients (FirstName, Surname, DOB)
    OUTPUT inserted.* INTO @IDS
    SELECT * FROM @Data

    SELECT * FROM @IDS  --Fills datatable in C# with returned data
END
GO

Any help would be greatly appreciated.

A ツ · Answer 1 · 2015-02-24T12:25:28.147

0

how about this:

DECLARE @IDS TABLE (ID INT, FirstName NVARCHAR(50), Surname NVARCHAR(50), DOB DATETIME2);

INSERT INTO @IDS (Id, FirstName, Surname, DOB)
SELECT c.ID, c.FirstName, c.Surname, c.DOB 
FROM @Data d 
JOIN Clients c ON c.FirstName=d.FirstName AND c.SurName=d.SurName AND c.DOB=d.DOB

INSERT INTO Clients (FirstName, Surname, DOB)
OUTPUT inserted.* INTO @IDS
SELECT d.FirstName, d.Surname, d.DOB 
FROM @Data d 
LEFT JOIN Clients c ON c.FirstName=d.FirstName AND c.SurName=d.SurName AND c.DOB=d.DOB
WHERE c.ID IS NULL;

SELECT * FROM @IDS  --Fills datatable in C# with returned data

edited Feb 24 '15 at 12:25

answered Feb 24 '15 at 10:53

A ツ

1,267
2
9
14

Thanks for that, I had to make one minor change. Rather than SELECT * FROM @Data d I had to specify FirstName, Surname and DOB only. – bowfinger Feb 24 '15 at 12:15

Returning PK's to C# using Table-Valued Parameters

1 Answers1