Merging two sql server DBs

Hi All,
i want to merge two sql server DBs (SourceDB,DestinationDB).
there could be some common tables between SourceDB and DestinationDB.
if that happens than i want sourceDb to overrite Destination DB table.
i know i can do this using exportWizard/DTS.
but problem with this is my source DB is huge and export wizard takes lot of
time.
i do not think i can do this using deattach\attach DB or using backup restor
e because both of this will overrite the detination database where as i want
to merge the two.
this is a one time job.
any ideas how i can do this.
thanks
siddharthHi
Are both databases's tables identical?
If they are you can create a view that contains data set of these tables
CREATE VIEW v_myview
AS
SELECT dbname1.dbo.table
UNION --or UNION ALL
SELECT dbname2.dbo.table
GO
SELECT * FROM v_myview
> if that happens than i want sourceDb to overrite Destination DB table
SELECT * INTO dbname2.dbo.NewTable FROM dbname1.dbo.table
GO
DROP TABLE dbname2.dbo.table
GO
--Run on dbname2
EXEC sp_rename 'NewTable','TABLE'
"siddharth" <anonymous@.discussions.microsoft.com> wrote in message
news:D9C89088-714B-4A6B-8AF8-10E0351EBF8F@.microsoft.com...
> Hi All,
> i want to merge two sql server DBs (SourceDB,DestinationDB).
> there could be some common tables between SourceDB and DestinationDB.
> if that happens than i want sourceDb to overrite Destination DB table.
> i know i can do this using exportWizard/DTS.
> but problem with this is my source DB is huge and export wizard takes lot
of time.
> i do not think i can do this using deattach\attach DB or using backup
restore because both of this will overrite the detination database where as
i want to merge the two.
> this is a one time job.
> any ideas how i can do this.
> thanks
> siddharth
>|||Hi
It sounds like you have a source and destination database the wrong way arou
nd! if you restored what you currently call the source database onto the des
tination server, you would then only need to more from the destination datab
ase the objects that are no
t in the source database! It would be possible to do this from the system ta
bles. DTS may still be an option to transfer the data or alternatively a DMO
program. If you don't have indexes, primary keys etc a straight forward SEL
ECT INTO statement would be
possible.
Alternatively you may want to look at something like the red gate tools to s
ee if they fulfil your requiremets:
http://www.red-gate.com/sql/summary.htm
John
-- siddharth wrote: --
Hi All,
i want to merge two sql server DBs (SourceDB,DestinationDB).
there could be some common tables between SourceDB and DestinationDB.
if that happens than i want sourceDb to overrite Destination DB table.
i know i can do this using exportWizard/DTS.
but problem with this is my source DB is huge and export wizard takes lot of
time.
i do not think i can do this using deattach\attach DB or using backup restor
e because both of this will overrite the detination database where as i want
to merge the two.
this is a one time job.
any ideas how i can do this.
thanks
siddharth

Merging two dbs

Hi
I have two sql server 2005 dbs on server A. I need to copy all items
(tables, view, sps and so on) from the two dbs into a single db on server B.
The added problem is that server B is SQL Server 2000 so direct copy is
perhaps not possible. How can I achieve this?
Thanks
RegardsYou can script out all the objects and try running on the 2000 Server|||The easiest way would be to add an instance of 2005 on that server and
simply copy them<g>. But assuming that is not an option and you have not
used any 2005 specific features or datatypes you can still do it. First
script all the objects using SSIS and then run those scripts in the other
db. Then export the data from the 2005 dbs and import them into the tables
in the 200 instance. Make sure to backup everything first.
Andrew J. Kelly SQL MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23AinXisUGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Hi
> I have two sql server 2005 dbs on server A. I need to copy all items
> (tables, view, sps and so on) from the two dbs into a single db on server
> B. The added problem is that server B is SQL Server 2000 so direct copy is
> perhaps not possible. How can I achieve this?
> Thanks
> Regards
>
>

Merging two dbs

Hi
I have two sql server 2005 dbs on server A. I need to copy all items
(tables, view, sps and so on) from the two dbs into a single db on server B.
The added problem is that server B is SQL Server 2000 so direct copy is
perhaps not possible. How can I achieve this?
Thanks
Regards
The easiest way would be to add an instance of 2005 on that server and
simply copy them<g>. But assuming that is not an option and you have not
used any 2005 specific features or datatypes you can still do it. First
script all the objects using SSIS and then run those scripts in the other
db. Then export the data from the 2005 dbs and import them into the tables
in the 200 instance. Make sure to backup everything first.
Andrew J. Kelly SQL MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23AinXisUGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Hi
> I have two sql server 2005 dbs on server A. I need to copy all items
> (tables, view, sps and so on) from the two dbs into a single db on server
> B. The added problem is that server B is SQL Server 2000 so direct copy is
> perhaps not possible. How can I achieve this?
> Thanks
> Regards
>
>

Merging two dbs

Hi
I have two sql server 2005 dbs on server A. I need to copy all items
(tables, view, sps and so on) from the two dbs into a single db on server B.
The added problem is that server B is SQL Server 2000 so direct copy is
perhaps not possible. How can I achieve this?
Thanks
Regards
The easiest way would be to add an instance of 2005 on that server and
simply copy them<g>. But assuming that is not an option and you have not
used any 2005 specific features or datatypes you can still do it. First
script all the objects using SSIS and then run those scripts in the other
db. Then export the data from the 2005 dbs and import them into the tables
in the 200 instance. Make sure to backup everything first.
Andrew J. Kelly SQL MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23AinXisUGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Hi
> I have two sql server 2005 dbs on server A. I need to copy all items
> (tables, view, sps and so on) from the two dbs into a single db on server
> B. The added problem is that server B is SQL Server 2000 so direct copy is
> perhaps not possible. How can I achieve this?
> Thanks
> Regards
>
>

Merging two dbs

Hi
I have two sql server 2005 dbs on server A. I need to copy all items
(tables, view, sps and so on) from the two dbs into a single db on server B.
The added problem is that server B is SQL Server 2000 so direct copy is
perhaps not possible. How can I achieve this?
Thanks
RegardsThe easiest way would be to add an instance of 2005 on that server and
simply copy them<g>. But assuming that is not an option and you have not
used any 2005 specific features or datatypes you can still do it. First
script all the objects using SSIS and then run those scripts in the other
db. Then export the data from the 2005 dbs and import them into the tables
in the 200 instance. Make sure to backup everything first.
Andrew J. Kelly SQL MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23AinXisUGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Hi
> I have two sql server 2005 dbs on server A. I need to copy all items
> (tables, view, sps and so on) from the two dbs into a single db on server
> B. The added problem is that server B is SQL Server 2000 so direct copy is
> perhaps not possible. How can I achieve this?
> Thanks
> Regards
>
>