CODEWITHSUNDEEP
sqlsql-serversql-server-2008vb6
DoreenSly
DoreenSly

Reputation: 37

Copying Data between two similar Databases

I have two databases, Database A and Database B, which are the same except for about few new columns in some tables in Database B.

The two databases contain the same number of tables (1160 tables). I added columns to about 40 tables in Database B. That is the only thing different from Database A.

I have emptied all tables in Database B and want to insert all data in each tables of Database A to Database B, leaving only the 40 tables that have the number of columns different from the source Database A.

I need a script to loop through all tables in Database A, check if the columns are the same, then copy its contents to that table in Database B. If the columns are not the same, it should leave an error of the table name.

Upvotes: 1

Views: 68

Answers (1)

FLICKER
FLICKER

Reputation: 6683

This should work, assuming all your tables are DBO. in case you have identity field, see my comment

-- select only tables that have the same number of columns
declare tbls cursor for with tblsA as (
select t.name, count(*) as colCount
from DatabaseA.sys.tables t
    inner join DatabaseA.sys.columns c on c.object_id = t.object_id
group by t.name
)
, tblsB as (
select t.name, count(*) as colCount
from DatabaseB.sys.tables t
    inner join DatabaseB.sys.columns c on c.object_id = t.object_id
group by t.name
)
select tblsA.name as TableName
from tblsA 
   inner join tblsB on tblsB.name = tblsA.name and tblsB.colCount = tblsA.colCount


declare @tblName varchar(100)
declare @sqlText nvarchar(max)
open tbls
fetch next from tbls into @tblName
while @@FETCH_STATUS = 0
begin
    print 'Inserting into ' + @tblName 

    --un-comment if you don't have identity fields. 
    --you will need more code here if some table have identity field and some don't
    --set @sqlText = 'SET IDENTITY_INSERT DatabaseB..' + @tblName + ' ON'
    --exec(@sqlText)

    set @sqlText = 'insert into DatabaseB..' + @tblName + ' select * from DatabaseA..' + @tblName
    exec(@sqlText)

    fetch next from tbls into @tblName
end

close tbls
deallocate tbls

Upvotes: 1

Related Questions