data merge

[dpu]

I have 2 databases that I need to merge into one. Can anyone tell me what the easiest way to do this is on SQL?

 

[SQLDenis]

run select * into db1.dbo.table from db2.dbo.table for all your tables

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[druer]

Denis query assumes that you don't have any of the tables from DB2 in DB1 already. Assuming that is true and you have more than a handful of tables that you could easily move yourself the following will iterate all of your DB2 tables and dynamically build the command to move them (I used a print statement in my test instead of actually executing, but I think it will work as I use something similar for reindexing)(The [ ] brackets are to ensure that if you have any weird table names like inventory-tbl, they are just enclosed):

declare curTables cursor for
select sysobjects.name from sysobjects where sysobjects.xtype = 'U'
declare @TableName varchar(255)

open curTables

fetch next from curTables into @TableName

while @@fetch_status = 0
begin
declare @command varchar(1000)
set @command = 'select * into db1.dbo.[' + @TableName + '] from db2.dbo.[' + @TableName + ']'
EXECUTE sp_executesql @command
fetch next from curTables into @TableName
end

close curTables
deallocate curTables

 

[amrita418]

You can use DTS to copy tables/data by checking all tables applicable.

Regards,
AA