Arthur Fuller
fuller.artful at gmail.com
Tue Mar 17 12:42:51 CDT 2009
Which version of SQL are you using? If > 2000, try this. It's not your answer but should get you most of the way there, at least as regards the columns: <sql> select * from INFORMATION_SCHEMA.COLUMNS -- and select * from INFORMATION_SCHEMA.VIEWS </sql> These might be SQL 2008, I can't remember whether they existed in 2005 and it's not installed any more so I can't readily check. A. Some time back I wrote a SQL Tip for TechRepublic on how to walk a set of rows and concatenate the column values into a comma-delimited list. I'll see if I can find it. It's not complex. It uses COALESCE to do the work. Meanwhile you might visit TechRepublic and search for something like "Arthur Fuller COALESCE". I'll search for it locally. On Tue, Mar 17, 2009 at 12:54 PM, John W Colby <jwcolby at gmail.com> wrote: > 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 > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >