0

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;

2 Answers 2

1

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).

1
  • That is part of the problem...dbOld tables had no primary keys...dbNew has primary keys.
    – HotTomales
    Commented Mar 6, 2020 at 15:05
0

Something like this should do the trick, assuming that the schemas are identical

insert dbNew.dbo.table
   select *
      from dbOld.dbo.table
      except (
         select *
            from dbNew.dbo.table)
1
  • See my edit - I tried to implement a cursor to automate the process....
    – HotTomales
    Commented Mar 6, 2020 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.