[dba-SQLServer] Any gut feelings?

I guess I wrote this a little too quickly.  What I meant is, does SQL have
anything like Access where you can with a query build a new table based on a
handful of fields fro the source table, and having done that, have the
destination field retain at least the same datatypes / data sizes in the
destination table.  And further can the source tables be out in one SQL
Server database, and destination table be inside of a second SQL Server
database.

I am having to cut and paste fields to the windows paste buffer, then switch
to a different (new) table and paste them in in order to get the same fields
and same field structures.  Even then I am doing this inside of a common
database because I don't know how to reference tables across different
databases.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Friday, November 03, 2006 3:55 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Any gut feelings?

So question:  Is it possible to spread tables out across databases and use
queries that join tables in other databases (inside the same SQL Server
instance)?  The reason I ask is that I have all of these raw data tables,
which I need, need to keep around, but don't need active.  It would be handy
(for size of database files reasons) to have the raw source tables out in
their own database and the finished processed, indexed tables in another
table.  Getting the raw data into the finished data table is the issue.
Exporting 65 million records, even a few fields, to a csv file or the like
is not exactly dba friendly.


John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
artful at rogers.com
Sent: Friday, November 03, 2006 1:32 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Any gut feelings?

It is split across 8 servers, each running two processors and each having >
2 GB of RAM. In part this arrangement was due to security concerns, which I
won't go into, but also the sheer size of the database made it impossible to
put it on one conventional server. All the servers run Windows Server 2003.
The first iteration ran on SQL 2000, but the conversion to SQL 2005 is
underway. The fact of 8 databases, each on a different server, exposed a
serious down side as compared to a similar implementation in Oracle -- MS
SQL cannot do referential integrity constraints across databases, so a bunch
of additional code + duplicate lookup tables were required. In Oracle, one
could simply declare tablespaces then place them on different servers, and
it would still remain a single database. Until this project, I had never
been involved with anything so large, so the issue never arose.