Insert Records That Do Not Exist We are upgrading from one database to another.  Both databases have the same tables, and for examples sake let's say we have dbOld and dbNew.  I need a way of


  for each table on dbNew check the table on dbOld - insert all rows that exist in dbOld into dbNew


How would I do this through code?

EDIT
This is code on what I'm looking at.  Obviously there are issues, but would this be a good approach to take?

Declare @tablename varchar(max)

Declare cursor_tables Cursor

For SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE' 
    AND TABLE_CATALOG='salesData' 

OPEN cursor_tables

FETCH NEXT FROM cursor_tables INTO @tablename;

 WHILE @@FETCH_STATUS = 0
 BEGIN
   insert dbNew.dbo. + @tablename
   select *
      from dbOld.dbo. + @tablename
      except (
      select *
        from dbNew.dbo. + @tablename)

FETCH NEXT FROM cursor_tables INTO @tablename;
END;
You can not give object names from variables in TSQL, so you have to be a bit hacky and make the statement as a string and run as ad-hoc SQL. Something like:

SET @SQL = '
        INSERT dbNew.dbo. + @tablename
        SELECT * FROM dbOld.dbo.' + @tablename + '
        EXCEPT 
        SELECT * FROM dbNew.dbo.' + @tablename + '
        '
EXEC (@SQL)


You are likely to find the following more efficient:

SET @SQL = '
        INSERT dbNew.dbo. + @tablename
        SELECT * FROM dbOld.dbo.' + @tablename + ' oTbl
        EXCEPT 
        LEFT OUTER JOIN dbNew.dbo.' + @tablename + ' nTbl
        ON nTbl.primaryKey = oTlb.primaryKey
        '
EXEC (@SQL)


but that does mean you need to work out what the primary key columns are by querying more INFORMATION_SCHEMA/sys.* structures in your cursor.

Another complication that you will hit in a well designed database is that you will need to order the table population to ensure that foreign key constraints are always satisfied during the process (or remove such constraints first and readd them after the transfer).