[AccessD] Dynamically build / populate a table in SQL Server

John W Colby jwcolby at gmail.com
Tue Mar 17 11:54:10 CDT 2009


I run jobs where I build up a data table with fixed fields.  I build a 
view to select records based on selection criteria provided by my 
client.  The selection criteria fields can be one or a dozen different 
criteria fields.  The result is a set of records placed into a new table.

I have a standard field list for the main data and a query that builds 
this tblOrderData on-the-fly.

The criteria fields change with every order.  I want to add these fields 
to the tblOrderData automagically, and then to populate tblOrderData 
with the values in these criteria fields, and finally to build an index 
of the selection criteria plus the PK.

What I need to know is if it is possible to somehow get the field list 
from a view and use that field list to create dynamic queries to create 
the fields, and to update the values into those fields once they are 
added to my standard tblOrderData.  And finally I need to create indexes 
on these added criteria fields in tblOrderData.

1) Somehow take a view with the criteria fields to use as a "field list"
2) Append these fields to an existing (empty) table.
3) Update those fields once the table is populated with records.
4) Add an index that contains these fields.

I need to do all of this in stored procedures.  I know how to build 
queries that add fields to a table, add an index to a table etc.  I do 
this all of the time.  What I am looking for is the way to get a field 
list of a view, and to somehow iterate that list to turn it into a 
dynamic "string of fields" to use in my queries (stored procedures) 
which add fields / update fields / index fields to tblOrderData.

This would be a huge piece of my processing moved from the "manual" side 
to the "automatic" side.

BTW, I currently do this by opening a set of stored procedures and 
manually editing the field lists of three stored procedures.  The 
"manual" method.  I have to remember to do this, and it is just ugly.

What would be really cool would be to open a field list for a view that 
I use for my selection criteria, open a field list of the standard 
fields in tblOrderData, and "subtract" the fields in tblOrderData from 
vSelectionCriteria, leaving me just a list of the fields in 
vSelectionCriteria not in tblOrderData.

All of this done in a stored procedure.

TIA for any responses.

-- 
John W. Colby
www.ColbyConsulting.com

-- 
John W. Colby
www.ColbyConsulting.com




More information about the AccessD mailing list