Newbie - enumerating DBs & tables

  • I have 2 servers, Test & Prod. Prod has all the data.

    Test's applications and DBs have already been upgraded to MyApplication v6.2. Prod's is being upgraded to v6.1.1.

    After Prod's upgrade we would like to copy the data from the

    Prod DBs to Test, except that with the version change/difference, I cannot easily just overwrite the Test DBs with the after upgrade prod DB backups. Tables & Fields may have been added, etc.

    Is there a way to enumerate the DBs then enumerate the tables

    maybe using nested T-SQL statements?

    someting like:

    EXEC sp_MSforeachDB 'EXEC MSforeachtable INSERT INTO Prod.Table1 SELECT * from Test.Table1'

    I know my syntax is not correct, but I can't seem to find anything like this, anywhere? Can you help?

    Or maybe can you suggest an alternative course of action?

    Thanx in advance

    jmonty

  • I have never found an easy way to do this, but have had this situation many times...I usually set up a script like this for each table:

    insert into test.table1 (test column list)

    select matching column list from prod

    from prod.table1

    -- this works great if you have linked servers set up between the two.  Just match up the two column lists, using default values or constants to account for any test columns that are not in prod.

    -- I usually use table scripts from Enterprise Mgr or Query Analyzer to speed the script-building process.  I also at times use WHERE clauses to limit test data.

    --  Keep in mind that the tables in the script must be in order so that FK constraints are satisfied.

    Good Luck!

    Steve

  • There's a pretty cool tool made by Red Gate Software called SQLCompare.  It's pretty handy for stuff like this since you may have the same table with the same column name in both systems, but what if the data types are different?  Ouch.  This tool will actually script out the T-SQL you'd need to run to make them the same. 

    BTW: They also have a Data Compare tool too, in case you're interested...

    Cheers,

    Ken

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply