join many MDF into one

Hi,
I have a MSDE2000 installation.
There I have 120 MDF files \ databases with alltogether 10GB.
All databases have an identical structure (same tables etc.)
Is there a way to join all databases into one big database?
I would then attach this big database to an SQL2000 Server and run some
queries against it.
thank youSteffen
Are those tables which have the same stucture also contain the data?
"Steffen Meier" <mature4711@.hotmail.com> wrote in message
news:OMpFS7cVHHA.1552@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I have a MSDE2000 installation.
> There I have 120 MDF files \ databases with alltogether 10GB.
> All databases have an identical structure (same tables etc.)
> Is there a way to join all databases into one big database?
> I would then attach this big database to an SQL2000 Server and run some
> queries against it.
> thank you
>|||"Steffen Meier" <mature4711@.hotmail.com> wrote in message
news:OMpFS7cVHHA.1552@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I have a MSDE2000 installation.
> There I have 120 MDF files \ databases with alltogether 10GB.
> All databases have an identical structure (same tables etc.)
> Is there a way to join all databases into one big database?
> I would then attach this big database to an SQL2000 Server and run some
> queries against it.
> thank you
>
If you mean like detach them and attach them no.
Unfortunately you'll probably have to script together something to move the
data.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi Uri,
Yes, these tables also contain the data.
Actually the structure of all the 120 databases is 100% identical
These databases contain logfile-data from ISA-Server 2004 - one new database
per day is created...
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:eTC3OBdVHHA.4796@.TK2MSFTNGP05.phx.gbl...
> Steffen
> Are those tables which have the same stucture also contain the data?
>
> "Steffen Meier" <mature4711@.hotmail.com> wrote in message
> news:OMpFS7cVHHA.1552@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> I have a MSDE2000 installation.
>> There I have 120 MDF files \ databases with alltogether 10GB.
>> All databases have an identical structure (same tables etc.)
>> Is there a way to join all databases into one big database?
>> I would then attach this big database to an SQL2000 Server and run some
>> queries against it.
>> thank you
>>
>|||I was hoping that there is a way to bulk-export the data of all 120
databases to a textfile and then import it into a new database... has
MSDE2000 some command for bulk export to textfile ? (or any other format?)
thx
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schrieb im
Newsbeitrag news:es1wPFdVHHA.600@.TK2MSFTNGP05.phx.gbl...
>
> "Steffen Meier" <mature4711@.hotmail.com> wrote in message
> news:OMpFS7cVHHA.1552@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> I have a MSDE2000 installation.
>> There I have 120 MDF files \ databases with alltogether 10GB.
>> All databases have an identical structure (same tables etc.)
>> Is there a way to join all databases into one big database?
>> I would then attach this big database to an SQL2000 Server and run some
>> queries against it.
>> thank you
>>
> If you mean like detach them and attach them no.
> Unfortunately you'll probably have to script together something to move
> the data.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>|||You're on the right track. I would write a VB.NET application that
programmatically used SqlBulkCopy to import the data into the common
database. I expect it would take a long afternoon to write.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Steffen Meier" <mature4711@.hotmail.com> wrote in message
news:%235RkfbeVHHA.1000@.TK2MSFTNGP05.phx.gbl...
>I was hoping that there is a way to bulk-export the data of all 120
>databases to a textfile and then import it into a new database... has
>MSDE2000 some command for bulk export to textfile ? (or any other format?)
> thx
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schrieb im
> Newsbeitrag news:es1wPFdVHHA.600@.TK2MSFTNGP05.phx.gbl...
>>
>> "Steffen Meier" <mature4711@.hotmail.com> wrote in message
>> news:OMpFS7cVHHA.1552@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> I have a MSDE2000 installation.
>> There I have 120 MDF files \ databases with alltogether 10GB.
>> All databases have an identical structure (same tables etc.)
>> Is there a way to join all databases into one big database?
>> I would then attach this big database to an SQL2000 Server and run some
>> queries against it.
>> thank you
>>
>> If you mean like detach them and attach them no.
>> Unfortunately you'll probably have to script together something to move
>> the data.
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting
>> sql (at) greenms.com http://www.greenms.com
>|||"Steffen Meier" <mature4711@.hotmail.com> wrote in message
news:%235RkfbeVHHA.1000@.TK2MSFTNGP05.phx.gbl...
>I was hoping that there is a way to bulk-export the data of all 120
>databases to a textfile and then import it into a new database... has
>MSDE2000 some command for bulk export to textfile ? (or any other format?)
>
Look into BCP
> thx
>
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||DTS is the way to go.
--DatabaseAdmins.com
Remote DBA Services
"Greg D. Moore (Strider)" wrote:
> "Steffen Meier" <mature4711@.hotmail.com> wrote in message
> news:%235RkfbeVHHA.1000@.TK2MSFTNGP05.phx.gbl...
> >I was hoping that there is a way to bulk-export the data of all 120
> >databases to a textfile and then import it into a new database... has
> >MSDE2000 some command for bulk export to textfile ? (or any other format?)
> >
> Look into BCP
>
> > thx
> >
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>|||yeah it's called psuedodynamic sql
select 'insert into Destination.Dbo.Mytable Select * FROM ' + name +
'.dbo.Source'
from master.dbo.sysdatabases
if you've got to get a different table name in each database; then do a join
to sysobjects
"Steffen Meier" <mature4711@.hotmail.com> wrote in message
news:%235RkfbeVHHA.1000@.TK2MSFTNGP05.phx.gbl...
> I was hoping that there is a way to bulk-export the data of all 120
> databases to a textfile and then import it into a new database... has
> MSDE2000 some command for bulk export to textfile ? (or any other format?)
> thx
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schrieb im
> Newsbeitrag news:es1wPFdVHHA.600@.TK2MSFTNGP05.phx.gbl...
> >
> >
> > "Steffen Meier" <mature4711@.hotmail.com> wrote in message
> > news:OMpFS7cVHHA.1552@.TK2MSFTNGP05.phx.gbl...
> >> Hi,
> >>
> >> I have a MSDE2000 installation.
> >> There I have 120 MDF files \ databases with alltogether 10GB.
> >> All databases have an identical structure (same tables etc.)
> >>
> >> Is there a way to join all databases into one big database?
> >> I would then attach this big database to an SQL2000 Server and run some
> >> queries against it.
> >>
> >> thank you
> >>
> >>
> >
> > If you mean like detach them and attach them no.
> >
> > Unfortunately you'll probably have to script together something to move
> > the data.
> >
> >
> >
> > --
> > Greg Moore
> > SQL Server DBA Consulting
> > sql (at) greenms.com http://www.greenms.com
> >
>