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

Drew Wutka DWUTKA at Marlow.com
Sun Aug 9 12:59:03 CDT 2009


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).

-- 
John W. Colby
www.ColbyConsulting.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
The information contained in this transmission is intended only for the person or entity 
to which it is addressed and may contain II-VI Proprietary and/or II-VI Business 
Sensitive material. If you are not the intended recipient, please contact the sender 
immediately and destroy the material in its entirety, whether electronic or hard copy. 
You are notified that any review, retransmission, copying, disclosure, dissemination, 
or other use of, or taking of any action in reliance upon this information by persons 
or entities other than the intended recipient is prohibited.





More information about the AccessD mailing list