[AccessD] Crossposted - Building a table dynamically - SQL Server

Drew,

How do you "flatten it back out" to pull a million records stored that way?  And how do you query 
the structure.My solution works really well as it is.  I end up with a database per order, which I 
NEED because the client often asks for results from that exact same data set later (quite common), 
when I have only delivered a portion of the records to the client.

Inside each order database I end up with all of the queries and stored procedures (and data of 
course) required to pull the specific records, and specific fields.

I understand what you are doing, but I question whether it would be very easy to work with both when 
  filling the "table" and then when pulling specific records out of the "table".  It doesn't "feel" 
like a solution to this specific problem.

What I was hoping was that someone out there on our lists would have done something like I am doing, 
actually creating a normal table on the fly, but where the table has a variable set of fields, and 
discuss how to do that using temp tables in a stored procedures (in sql server).  Temp tables might 
not even be the right term, a cursor is more what I mean (I think).

I actually got all of this stuff working, but it is not as straightforward as I would wish.  Perhaps 
"if it ain't broke..." might very well apply here.

John W. Colby
www.ColbyConsulting.com


Drew Wutka wrote:
> Yes, I call the process I use 'Dynamic Fields'.
> 
> It is a static structure, that handles a dynamic structure.
> 
> Example one, our old website's 'product database' could require any
> number of fields for product data.  One product might have a height, a
> weight, a width and a power requirement.  One product might have a
> color, material type, etc.  So instead of building a table for each type
> of product, I built a structure that allowed for ANY number of fields.
> 
> To do this, I created several data tables.  These tables have three
> fields.  A 'field' field, an 'ID' field, and a 'value' field.  There was
> a table for each value type that I wanted to use (ie, currency, long
> integer, text, date, memo, double).  I then created a 'fields' table,
> which would define the fields used, and would link to the data tables
> with the 'field' field (using the PK from the fields definition table).
> The ID field in the data tables would then relate to the Product table
> which only housed very generic data, such as the Product name.
> 
> Does that make sense?  I have used this process in several systems that
> I have built, where I require a dynamic table structure.
> 
> Drew
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Friday, August 07, 2009 4:13 PM
> To: Dba-Sqlserver; Access Developers discussion and problem solving
> Subject: [AccessD] Crossposted - Building a table dynamically - SQL
> Server
> 
> 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).
>