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.