sql server – What would be the best way to conditionally insert or update a table that has this unique design?

I have an items table that has columns for person ID, position #, and individual lists of items that really aren’t associated with the other fields in the record.

Here’s an example of what it looks like (T-SQL at the end of my question):

PersonId    Pos         FavoriteColors  AllGifts        BirthDayGifts   HolidayGifts
----------- ----------- --------------- ------------------------------- -------------
1234        1           Blue            Mug             Mug             Computer
1234        2           Green           Phone           Candle          Phone
1234        3           Purple          Candle          Boardgame       NULL
1234        4           Yellow          Computer        Basketball      NULL
1234        5           Orange          Boardgame       NULL            NULL
1234        6           Red             Basketball      NULL            NULL
1234        7           Gray            NULL            NULL            NULL
1234        8           Brown           NULL            NULL            NULL
2222        1           Yellow          NULL            NULL            NULL
4118        1           Pink            Clothes         Handsoap        Clothes
4118        2           Green           Handsoap        NULL            NULL

I realize this is not great table design. In any case, the situation is that every person gets a Book for a holiday gift. Also, there’s a new person (4442) who also received a book.

Based on how this table works, the HolidayGifts and AllGifts columns need have “Book” added as a new value. This may require an INSERT, UPDATE, or both…

The resulting table would look like this:

PersonId    Pos         FavoriteColors  AllGifts        BirthDayGifts   HolidayGifts
----------- ----------- --------------- ------------------------------- -------------
1234        1           Blue            Mug             Mug             Computer
1234        2           Green           Phone           Candle          Phone
1234        3           Purple          Candle          Boardgame       Book
1234        4           Yellow          Computer        Basketball      NULL
1234        5           Orange          Boardgame       NULL            NULL
1234        6           Red             Basketball      NULL            NULL
1234        7           Gray            Book            NULL            NULL
1234        8           Brown           NULL            NULL            NULL
2222        1           Yellow          Book            NULL            Book
4118        1           Pink            Clothes         Handsoap        Clothes
4118        2           Green           Handsoap        NULL            Book
4118        3           NULL            Book            NULL            NULL
4442        1           NULL            Book            NULL            Book

In the case of person 1234, this means:

  • an Update statement to the HolidayGifts column where PersonId = 1234 and Pos = 3 (the pos of the first null value for the HolidayGifts column)
  • an Update statement to the AllGifts column where PersonId = 1234 and Pos = 7 (the pos of the first null value for the AllGifts column)

In the case of person 2222, this means:

  • same as 1234–update the first null value for the HolidayGifts and AllGifts columns. They both happen to be pos = 1, so in this particular case, it could be done in one statement where PersonId = 2222 and pos = 1…

In the case of person 4118:

  • an update needs to happen to the HolidayGifts column where PersonId = 4118 and Pos = 2 (the first null value of the HolidayGifts column)

AND

  • an insert needs to happen (with a personid = 2222, pos = 3 and AllGifts = “Book”) because AllGifts doesn’t have any NULL values at the end for this person.

In the case of person 4442:

  • They’re brand new, so they’re a simple insert with values for PersonId, POS, HolidayGifts, and AllGifts.

I hope I’m explaining myself makes sense. I realize the way this work is a bit awkward, but I have to imagine there’s a clever way (or any way) to accomplish this if a PersonId and gift are passed in as a parameter?

Also, here’s the T-SQL to create the before and after tables.

Thank you in advance!

Mike

DECLARE @ItemsBefore AS TABLE 
  ( 
     PersonId       INT NULL, 
     Pos            INT NULL, 
     FavoriteColors VARCHAR(100) NULL, 
     AllGifts       VARCHAR(100) NULL, 
     BirthDayGifts  VARCHAR(100) NULL, 
     HolidayGifts   VARCHAR(100) NULL 
  ) 

INSERT @ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 1, N'Blue', N'Mug', N'Mug', N'Computer')
INSERT @ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 2, N'Green', N'Phone', N'Candle', N'Phone')
INSERT @ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 3, N'Purple', N'Candle', N'Boardgame', NULL)
INSERT @ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 4, N'Yellow', N'Computer', N'Basketball', NULL)
INSERT @ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 5, N'Orange', N'Boardgame', NULL, NULL)
INSERT @ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 6, N'Red', N'Basketball', NULL, NULL)
INSERT @ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 7, N'Gray', NULL, NULL, NULL)
INSERT @ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 8, N'Brown', NULL, NULL, NULL)
INSERT @ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (4118, 1, N'Pink', N'Clothes', N'Handsoap', N'Clothes')
INSERT @ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (4118, 2, N'Green', N'Handsoap', NULL, NULL)
INSERT @ItemsBefore (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (2222, 1, N'Yellow', NULL, NULL, NULL)

SELECT *
FROM   @ItemsBefore
ORDER  BY PersonId, Pos

DECLARE @ItemsAfter AS TABLE 
  ( 
     PersonId       INT NULL, 
     Pos            INT NULL, 
     FavoriteColors VARCHAR(100) NULL, 
     AllGifts       VARCHAR(100) NULL, 
     BirthDayGifts  VARCHAR(100) NULL, 
     HolidayGifts   VARCHAR(100) NULL 
  ) 

INSERT @ItemsAfter (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 1, N'Blue', N'Mug', N'Mug', N'Computer')
INSERT @ItemsAfter (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 2, N'Green', N'Phone', N'Candle', N'Phone')
INSERT @ItemsAfter (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 3, N'Purple', N'Candle', N'Boardgame', N'Book')
INSERT @ItemsAfter (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 4, N'Yellow', N'Computer', N'Basketball', NULL)
INSERT @ItemsAfter (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 5, N'Orange', N'Boardgame', NULL, NULL)
INSERT @ItemsAfter (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 6, N'Red', N'Basketball', NULL, NULL)
INSERT @ItemsAfter (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 7, N'Gray', N'Book', NULL, NULL)
INSERT @ItemsAfter (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (1234, 8, N'Brown', NULL, NULL, NULL)
INSERT @ItemsAfter (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (4118, 1, N'Pink', N'Clothes', N'Handsoap', N'Clothes')
INSERT @ItemsAfter (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (4118, 2, N'Green', N'Handsoap', NULL, N'Book')
INSERT @ItemsAfter (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (4118, 3, NULL, N'Book', NULL, NULL)
INSERT @ItemsAfter (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (2222, 1, N'Yellow', N'Book', NULL, N'Book')
INSERT @ItemsAfter (PersonId, Pos, FavoriteColors, AllGifts, BirthDayGifts, HolidayGifts) VALUES (4442, 1, NULL, N'Book', NULL, N'Book')

SELECT *
FROM   @ItemsAfter
ORDER  BY PersonId, Pos