Stuart McLachlan
stuart at lexacorp.com.pg
Fri Aug 7 20:32:49 CDT 2009
As I see it, you only need two SQL queries - a "Create Table" which inserts the relevant
fields and indexes and an "Insert Into... From Select(.......)" to populate the table.
The key is selecting field names and maniplating strings to build the SQL. So you need a
tool which is good at both of these. Access would work well because of it's multilselect
listbox and VBA's string handling - so I am cross-posting this back to AccessD. :-)
Create an Access database with a link to your main data table in SQL Server.
Start off with a Form containing a multiselect list
Fill the listbox with all the fields from your master database with something like
Function GetFields()
Dim db As Database
Dim tbl As TableDef
Dim fd As Field
Set db = CurrentDb
Set tbl = db.TableDefs("dbo_tblMasterData")
For Each fd In tbl.Fields
lstFields.Add fd.Name
Next
End Function
Select the fields you want for youe new tblOrderData.
Write a function which loops through the selected items and builds an SQL "create table"
string to create the table with the appropriate fields and indexes and an "insert into" string to
populate the table. You can make these dynamic queries or "Create Procedure" queries.
Export the strings to .qry files and then run them in SQL Server Management Studio.
(Personally, I'd use PB/WIN and SQL Tools - that way I could do it all in one small .exe file
including runnning the "Create Table" and cross database "Insert Into" queries without
needing export the strings to file and importing them into SQL Server management Studio)
--
Stuart
On 7 Aug 2009 at 17:13, jwcolby wrote:
> I need to build a table (tblOrderData) to permanently store all of the data from all of the fields
> for an order. The table consists of two parts:
>
> 1) A PK and all of the name address fields. This is fixed and never changes.
> 2) A dynamic part where the fields come from a query used to select the name / address data, and
> later to insert said data into this tblOrderData. This part can be anywhere from 1 to N fields from
> about 600 possible criteria fields (the database from hell for those who have followed my past
> ramblings).
>
> As a little background, I have a template database with all of the queries, tables and stored
> procedures required to build an order, and then I copy that template to an order name, run the order
> and end up with a permanent store of all the records that go into an order. The permanent storage
> and database per order is non-negotiable, so please no "normalize it" suggestions.
>
> This template has become overstuffed from trying this that and the other so I am rebuilding it,
> keeping just what is actually used.
>
> So...
>
> I have a stored procedure that builds the fields of the base tblOrderData. Just because this is
> what occurred to me to do for this, I then take the fields of the selection view and subtract the
> fields from the base tblOrderData, and end up with a field list (and data type - mostly just
> varchar) for the fields that have to be added on to tblOrderData to store all of the data required
> to process an order.
>
> This subtraction process uses the Information_Schema table to obtain the field name and data type
> for tblOrderData (the base table) and a specific view which is the actual view used to populate the
> table later on. Outer join / not in and voila, just the names of the criteria fields. I use that
> in a dynamic SQL statement to append fields to tblOrderData.
>
> tblOrderData plus N various criteria fields makes new tblOrderData with every single field required
> to process the order.
>
> I do this in a set of stored procedures. The process requires (or at least I THINK it requires)
> creating a handful of views dynamically as well. The process is more complex than I would like
> however, though it does "just work".
>
> I am looking for suggestions for how to build this dynamic tblOrderData. I do not use (not sure
> what they are called) temporary tables inside of stored procedures, but this certainly seems like a
> good place to do that. Instead of dynamically creating views and using those views to create lists
> of fields, and using those lists of fields to append new fields to the base table, just use a single
> stored procedure and a handful of "temporary tables" to get the field list and append the new
> fields. Of course I also have to dynamically build indexes over these fields as well...
>
> So from this disjointed rambling, does anyone have any cogent suggestions re "a better way" to
> dynamically build a table with a base part (fields) and a changing part (fields).
>
> --
> John W. Colby
> www.ColbyConsulting.com
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>