Build SQL string looping through field names

[WC Justice]

I need to build an UPDATE statement that copies the values of roughly 40
fields from a table that stores standard or default values into a table of
specific contracts. There are 8 or so fields in the source table that are
not in the destination table. The remaining field names are identical in
both tables. How do I create a recordset of the field names I am interested
in so that I can loop through it to build my UPDATE statement?

Thank you

 

[Bob Barrows [MVP]]

I need to build an UPDATE statement that copies the values of roughly
40 fields from a table

Oops - you forgot to tell us what type and version of database you are
using. I Suspect Access, but it would be nice to be sure.

that stores standard or default values into a
table of specific contracts.

Simple answer:

Don't

Don't store the same data twice. You are likely to be using a relational
database here. By storing te same data twice, you are interfering with the
ability of the rdbms to preserve data integrity.

Instead of copying all these values to a separate table, store a link to the
standard values.

The only reason not to do this is if you need to track historicity, i.e.,
you need to know what the values were at the time the record was created.
But even this can be done without copying the data by using EffectiveDate
and DiscontinueDate columns in the standards table.

There are 8 or so fields in the source
table that are not in the destination table. The remaining field
names are identical in both tables. How do I create a recordset of
the field names I am interested in so that I can loop through it to
build my UPDATE statement?

If you're bound and determined, then you have a few options, which you can
read about here:http://www.aspfaq.com/show.asp?id=2177, although he did
leave out the OpenSchema option which you can read about here:
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthopenschema.asp


Bob Barrows

 

[Bob Barrows [MVP]]

Thanks for the response. This is an ASP-based website with a
Microsoft 2003 Windows Small Business Server SQL Server back end.

I respect your advice regarding duplicating of records, however it is
necessary in this case. Each contract contains these 40 provisions
which need to be editable at the contract level. When the user
indicates that he wants to create a new contract, one of the
subroutines attaches the default provisions to the new contract,
which are then edited as necessary or appropriate.

Why insert them at this point? Why not simply retrieve the values and
present them to the user in the editing page? Then, when he saves his edits,
write the final version of the contract into the database. Just a
suggestion.

I started hard
coding the SQL string but was hoping to avoid doing it for both the
INSERT and UPDATE statements.

I would hard-code it anyways. You can use QA (if you have it) to generate
the list of columns using one of the techniques in the aspfaq article.

A scan of the links you provided makes me feel that it may be a
little over my head, but if you think it will address my question, I
will study it until I get it.

Start studying

Bob Barrows