Insert if missing, example of joining tables to exclude duplicates.

Pete Sral

I ran across a question on StackOverflow, I did not understand the full scope of the question; however, I added my answer.   We run into situations when migrating data when there is a table that exists in both databases and there are duplicate values and we need to migrate only the missing values while excluding the duplicates without generating key violation errors.

See Visual Representation of SQL Joins by C.L. Moffatt for a good visual explanation of joins.

 

-- your Table2
Declare @table2 TABLE (age varchar(10), name varchar(10))

-- a temp variable table to join in order to exclude the duplicates
Declare @data TABLE (age varchar(10), name varchar(10))

    -- add all the values you want to verify the existence of
    INSERT INTO @data SELECT '0-19', 'abc'
    INSERT INTO @data SELECT '20-29', 'zxy'
    INSERT INTO @data SELECT '30-39', 'egt'
    INSERT INTO @data SELECT '40-49', 'aaa'
    INSERT INTO @data SELECT '50-59', 'rtg'
    INSERT INTO @data SELECT '60+', 'ghg'

    --  before the insert (all the missing values)
    SELECT d.age, d.[name] 
        FROM @data d 
            LEFT OUTER JOIN @table2 t ON d.age = t.age 
        WHERE t.age IS NULL

    -- do the insert
    INSERT INTO @Table2 
    SELECT d.age, d.[name] 
        FROM @data d 
            LEFT OUTER JOIN @table2 t ON d.age = t.age 
        WHERE t.age IS NULL

    -- this should now be empty
    SELECT d.age, d.[name] 
        FROM @data d 
            LEFT OUTER JOIN @table2 t ON d.age = t.age 
        WHERE t.age IS NULL

 

Leave a thought