Build SQL string looping through field names

Discussion in 'ASP General' started by WC Justice, Jan 24, 2005.

  1. WC Justice

    WC Justice Guest

    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
     
    WC Justice, Jan 24, 2005
    #1
    1. Advertising

  2. WC Justice wrote:
    > 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

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Jan 24, 2005
    #2
    1. Advertising

  3. WC Justice wrote:
    > 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
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Jan 24, 2005
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.

Share This Page