SQL Stored Procedure Avoid Duplicates in One to Many relationship

BigH61 581 Reputation points
2023-04-11T09:00:57.4666667+00:00

If you have a One to Many relationship and for the sake of this qustion it is as below, Where NameId is the PrimaryKey in the NameTable and BookId is the PrimaryKey in the BookTable.

enter image description here

As a stored procedure I can insert data as follows

PROCEDURE [dbo].[spInsertBooks]
@FirstName varchar(150), @LastName varchar(150), @BookTile varchar(500), @YearPublished varchar(4)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @NameId int
INSERT INTO NameTable (FirstName,LastName) VALUES (@FirstName, @LastName)
SELECT @NameId = Scope_Identity();
INSERT INTO BookTable (NameId, BookTitle, YearPublished) VALUES (@NameId, @BookTile, @YearPublished)
END

However this could result in duplicates in the NameTable. How would I ensure no duplicates in the Name table but the Book Data is still added along with the appropriate NameId. Thank you for your assistance

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,870 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 112.9K Reputation points
    2023-04-11T09:19:09.6066667+00:00

    Maybe like this:

    DECLARE @NameId int = (select top(1) NameId from NameTable where FirstName = @FirstName and LastName = @LastName)
    if @NameId is null
    begin
       INSERT INTO NameTable (FirstName, LastName) VALUES (@FirstName, @LastName)
       SELECT @NameId = Scope_Identity();
    end
    INSERT INTO BookTable . . .
    

    Probably it can be adjusted if you need parallel execution of multiple calls.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful