LISTING 2: Using a View to Find Missing Records

SET quoted_identifier OFF
GO
SET nocount ON
GO
/* Create production table, which includes missing rows from update table. */
CREATE TABLE production
        (field1 int primary key
        , data1 varchar(30))

/* Create update table, which includes rows not found in the production table. */
CREATE TABLE updateprod
        (field1 int primary key
        , data1 varchar(30))

/* Populate production table with sample data. */
INSERT production values(1, 'data 1')
INSERT production values(2, 'data 2')
INSERT production values(3, 'data 3')
INSERT production values(4, 'data 4')
INSERT production values(5, 'data 5')
INSERT production values(8, 'data 8') -- missing from updateprod(ignored)
INSERT production values(9, 'data 9')

/* Populate update table with sample data. */
INSERT updateprod values(1, 'data 1')
INSERT updateprod values(2, 'data 2')
INSERT updateprod values(3, 'data 3')
INSERT updateprod values(4, 'data 4')
INSERT updateprod values(5, 'data 5')
INSERT updateprod values(6, 'data 6')     -- missing from production
INSERT updateprod values(7, 'data 7')     -- missing from production
INSERT updateprod values(9, 'data 9')
INSERT updateprod values(10, 'data 10')   -- missing from production

GO
CREATE VIEW production_append
AS
SELECT updateprod.*
FROM 	updateprod
	LEFT OUTER JOIN production
	ON production.field1 = updateprod.field1
WHERE 	production.field1 IS NULL
GO

/* Populate production table with missing rows from the update table. */
INSERT INTO
        production
SELECT * FROM production_append

/* Display production table, which now has all the missing rows found
   in the update table. */
SELECT
        p.field1
        , p.data1
FROM
        production     p

GO

/* Clean up -- destroy tables and views. */
DROP TABLE production
DROP TABLE updateprod
DROP VIEW production_append