artful at rogers.com
artful at rogers.com
Fri Nov 3 19:02:29 CST 2006
Of course you can, JC. Assuming it's a repetitive operation, it's smarter to build the table first, but if not then you can build it dynamically. For the former, the paradigm is the same as Access: INSERT INTO NewTable SELECT this, that, theOther FROM T1 INNER JOIN T1.PK = T2.FK You might have to qualify said statement with column names and the VALUES predicate. If you need to create the table, then SELECT INTO NewTable is the approach. HTH, Arthur ----- Original Message ---- From: JWColby <jwcolby at colbyconsulting.com> To: dba-sqlserver at databaseadvisors.com Sent: Friday, November 3, 2006 4:27:37 PM Subject: Re: [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. _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com