[dba-SQLServer] Crossposted - Building a table dynamically - SQL Server

jwcolby jwcolby at colbyconsulting.com
Fri Aug 7 16:13:12 CDT 2009


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



More information about the dba-SQLServer mailing list