Post reply

inserting multiple records with one sql statement?

  • July 19, 2005 at 12:19 pm

    #575612

    #listlen(form.option1values, chr(13))#  returns the number of items in the list.

    and in that line i amsaying that if the counter is less than the length of the list, then output the line - union all.

    otherwise its at the end of the listt and we dotn need a union all after the last select.

  • July 19, 2005 at 1:17 pm

    #575627

    Only a couple of things come to mind:

    are you by any chance creating a temp table on the fly or do you already have a table in your database ?!

    also, is there any way you can explicitly set the length of your variable (option1values ??) to 30 ?







    **ASCII stupid question, get a stupid ANSI !!!**

  • July 19, 2005 at 1:46 pm

    #575642

    no its nto a temp table it is goign right into the live db table.

    and i am not sure if i can explicitly set the length of a CF variable - i dont think so but i will look into it.

  • July 20, 2005 at 2:33 am

    #575737

    Based on your example it seems that the length of MyValue is somehow determined by the first value in the list (in case of 'Red' is 3, in case of Large is 5) I would search in that area.

    Or an another hint (which doesn't really make sense for me you could test the following and tell us if it works:

    <cfset listcount = 0>

       <CFQUERY datasource="DWS_product_catalog">

        Insert Into option1values(ProdID, myValue)

        <cfloop list="#form.option1values#" index="currentValue" delimiters="#chr(13)#">

       <cfset #variables.listcount# = #variables.listcount# + 1>

       select #url.prodID#, cast('#Left(Ltrim(currentvalue),30)#' as varchar(30))

       <CFIF #variables.listcount# LT #listlen(form.option1values, chr(13))#>

         union all

       </CFIF>

        </cfloop>

      </CFQUERY>



    Bye
    Gabor

  • July 20, 2005 at 9:33 am

    #575841

    someone on the macromedia forum suggested that i use this format:

    Insert Into option1Values(ProdID, myValue)

    VALUES

    (1,'Red'),

    (1,'Orange'),

    (1,'Green')

    and it seems to work.

  • July 20, 2005 at 10:32 am

    #575864

    that worked well for my MySQL database but now i am tryign to do the same thing on an SQL Server 2000 db and it does not like that format.

    any suggestions on how i could insert multiple records with one SQL query using Microsoft SQL Server 2000?

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply