SQL Server 2005: Insert missing records in table that is in another reference table

Question

Hi to all,

I need help with the following. I have 2 tables. The first holds data captured by client. example.

[Data] Table

PersonId    Visit         Tested     Done
01          Day 1         Eyes       Yes
01          Day 1         Ears       Yes
01          Day 2         Eyes       Yes
01          Day 3         Eyes       Yes
02          Day 1         Eyes       Yes
02          Day 2         Ears       Yes
02          Day 2         Smell      Yes
03          Day 2         Eyes       Yes
03          Day 2         Smell      Yes
03          Day 3         Ears       Yes

and the second table holds info of what needs to be tested.

[Ref] Table

Visit      Test
Day 1      Eyes
Day 1      Ears
Day 1      Smell
Day 2      Eyes
Day 2      Ears
Day 2      Smell
Day 3      Eyes
Day 3      Ears
Day 3      Smell

now I'm trying to write an insert query on the [Data] to insert the non-existent tests that needed to be performed. The result I'm looking for example:

[Data] table after:

PersonId    Visit         Tested     Done
01          Day 1         Eyes       Yes
01          Day 1         Ears       Yes
01          Day 1         Smell      No
01          Day 2         Eyes       Yes
01          Day 2         Ears       No
01          Day 2         Smell      No
01          Day 3         Eyes       Yes
01          Day 3         Ears       No
01          Day 3         Smell      No
02          Day 1         Eyes       Yes
02          Day 1         Ears       No
02          Day 1         Smell      No
02          Day 2         Eyes       No
02          Day 2         Ears       Yes
02          Day 2         Smell      Yes
02          Day 3         Eyes       No
02          Day 3         Ears       No
02          Day 3         Smell      No
03          Day 1         Eyes       No
03          Day 1         Ears       No
03          Day 1         Smell      No
03          Day 2         Eyes       Yes
03          Day 2         Ears       No
03          Day 2         Smell      Yes
03          Day 3         Eyes       No
03          Day 3         Ears       Yes
03          Day 3         Smell      No

If needed it will be OK to create a third [results] table. All help will be much appreciated.

Kind Regards Jacques

Answer 1

I think you'll need a person table with just the personIDs, then you can do a cross join (full outer join) with your test ref table to come up with a schedule of personIDs and expected tests.

Then, with that schedule set, do an outer join with the set of tests performed on personIDs and expect nulls instead of no's.

Then, if you want, you can convert your nulls to 'no'.

Answer 2

This probably isn't the best way, but....What if you were to create a primary key on the [Data] table,

PK: (PersonID, Visit, Tested)

Then you could create a function to insert for each personID, and Day

CREATE PROCEDURE InsertTests
@PersonID int
, @Day nvarchar(10)

Begin

BEGIN TRY
INSERT INTO [Data]
(PersonID, Visit, Tested, Done)
VALUES
(@PersonID, @Day, Eyes, No)
END TRY
BEGIN CATCH
END CATCH

BEGIN TRY
INSERT INTO [Data]
(PersonID, Visit, Tested, Done)
VALUES
(@PersonID, @Day, Ears, No)
END TRY
BEGIN CATCH
END CATCH

BEGIN TRY
INSERT INTO [Data]
(PersonID, Visit, Tested, Done)
VALUES
(@PersonID, @Day, Smell, No)
END TRY
BEGIN CATCH
END CATCH

End

Answer 3

I'm suspicious of the database design if it requires this (along with some other red flags), but the following query should give you what you are asking for:

INSERT INTO Results
(
    person_id,
    visit,
    tested,
    done
)
SELECT
    P.person_id,
    T.visit,
    T.test,
    'No'
FROM
    (SELECT DISTINCT person_id FROM Results) P -- Replace with Persons table if you have one
CROSS JOIN Templates T
LEFT OUTER JOIN Results R ON
    R.person_id = P.person_id AND
    R.visit = T.visit AND
    R.test = T.test
WHERE
    R.person_id IS NULL

Or alternatively:

INSERT INTO Results
(
    person_id,
    visit,
    tested,
    done
)
SELECT
    P.person_id,
    T.visit,
    T.test,
    'No'
FROM
    (SELECT DISTINCT person_id FROM Results) P -- Replace with Persons table if you have one
INNER JOIN Templates T ON
    NOT EXISTS
    (
        SELECT *
        FROM
            Results R
        WHERE
            R.person_id = P.person_id AND
            R.visit = T.visit AND
            R.test = T.test
    )

Answer 4

Here's a perhaps-simpler solution using Common Table Expressions:

WITH allTestsForEveryone AS 
(
 SELECT *
 FROM (SELECT DISTINCT PersonID FROM DATA) a
 CROSS JOIN REF
),
allMissingTests AS 
(
 SELECT PersonID,Visit,Test FROM allTestsForEveryone
 EXCEPT
 SELECT PersonID,Visit,Tested FROM DATA
)
INSERT INTO [DATA] (PersonID, Visit, Tested, Done)
SELECT PersonID, Visit, Test, 0 AS Done FROM allMissingTests;

The first CTE (allTestsForEveryone) gives you a set of all tests needed on all days for all persons. In the second CTE (allMissingTests), we subtract the tests that have been taken using the EXCEPT operator, and add a '0' to represent their not-done status when we insert them (you can replace that with 'No' - when I ran this test I used a bit column). We then insert the results of the second CTE into Data.

Answer 5

INSERT Data
SELECT P.PersonID, R.Visit, D.Test, 'No'
FROM
   Person P -- or (SELECT DISTINCT PersonID FROM Data) P
   CROSS JOIN Ref R
WHERE
   NOT EXISTS (
      SELECT 1
      FROM Data D
      WHERE
         P.PersonID = D.PersonID
         AND R.Visit = D.Visit
         AND R.Test = D.Test
   )

And I can't resist posting a short version of @djacobson's answer:

ALTER TABLE Data ADD CONSTRAINT DF_Data_Done DEFAULT ('No')

INSERT Data (PersonID, Visit, Test)
SELECT P.PersonID, R.Visit, D.Test
FROM Person P CROSS JOIN Ref R
EXCEPT SELECT PersonID, Visit, Test FROM Data