ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

insert multiple rows

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • insert multiple rows

    Hi all,

    I want to insert multiple rows in my table.
    Why is it that i cannot execute this statement?:

    INSERT INTO myfile VALUES
    (140,'text1',20),
    (250,'text2',20)
    ...
    ...


    Does it mean i will be retyping the insert into statements..
    like this:

    INSERT INTO myfile VALUES
    (140,'text1',20)

    INSERT INTO myfile VALUES
    (250,'text2',20)

    ..
    ..


    Is there a workaround or shortcut?


    Thanks,
    Greg

  • #2
    Re: insert multiple rows

    Is this what you are looking for?

    Inserting multiple rows using the blocked INSERT statement

    publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/sqlp/rbafyblkins.htm

    Comment


    • #3
      Re: insert multiple rows

      See post number #5 ! ! !

      INSERT MULTIPLE row values instead of using multiple INSERT statements

      I read an article on IBM´s homepage on DB2 where they had a tip on how to insert multiple row values with one statement like this

      INSERT INTO [TABLE] values
      ('Value1','Fernando'),
      ('Value2','Carlos'),
      ('Value3','Vincent')


      Haven't test it.

      Give it a try !


      See post number #5 ! ! !
      Last edited by Marc_d; June 25, 2008, 03:14 AM. Reason: See post number 5

      Comment


      • #4
        Re: insert multiple rows


        They do it this way in MS SQL: A work around using multiple UNION ALL statements.


        See code bellow

        INSERT INTO[TABLE]
        select 'Value1','Fernando'
        UNION ALL
        select 'Value2','Carlos'
        UNION ALL
        select 'Value3','Vincent'

        As always, haven't test it, give it a try !

        Comment


        • #5
          Re: insert multiple rows

          from the SQLBIBLE



          DB2 UDB 8.1

          The main difference between DB2's INSERT and our generic syntax is the ability to insert multiple rows within a single VALUES clause of the INSERT statement. The following statement inserts four rows into STATUS table at once:

          INSERT INTO status
          (
          status_id_n,
          status_code_s,
          status_desc_s
          )
          VALUES
          ( 2, '20', 'COMPLETE'),
          ( 6, '60', 'SHIPPED'),
          ( 8, '70', 'INVOICED'),
          ( 9, '80', 'CANCELLED')


          As always, haven't test it, give it a try !

          Comment


          • #6
            Re: insert multiple rows

            Hi,

            Marc sample query is working fine.

            I tested it in my file which has one column.
            Code:
            INSERT INTO VINOMAILS1/ABC (ABC) VALUES('sfsf'),('sdf') 
            2 rows inserted in ABC in VINOMAILS1.
            Regards,
            Vinothkumar S.

            Comment


            • #7
              Re: insert multiple rows

              if you are on the iSeries and using STRSQL you only need to press F9 on the empty line and it will load the last statement. Just change the values you need to change, press Enter. No need to retype the whole statement over again
              Your future President
              Bryce

              ---------------------------------------------
              http://www.bravobryce.com

              Comment


              • #8
                Re: insert multiple rows

                The insert multiple rows using a single statement does not work on our box. We are on v5r2. It says 'Token , was not valid. Valid tokens: END-OF-STATEMENT.' on the next row values. I can only insert one row values at a time.

                what version are you Vino? maybe this is a version issue.

                Thanks,
                Greg

                Comment


                • #9
                  Re: insert multiple rows

                  Hi greg,

                  You are correct. I am using V5R3.

                  Even i tried in V5R2 machine. Its showing error.
                  Regards,
                  Vinothkumar S.

                  Comment


                  • #10
                    Running V7R1, from sql scripts in iSeries navigator I can run this: (skip the VALUES clause and use a subselect. I bet this works back to V5R4 too.)
                    (this is a subset of fields in #DSLD00002.CMFPMDD which has 64 fields, the subselect field-order / field-attrib have to match the insert field-order, field-attrib)

                    Insert INTO #DSLD00002.CMFPMDD ( MDMDF, MDCHGS, MDUAS, MDSRQ, MDMBR, MDATR , MDOBT, MDDSC, MDSEQ, MDAMG,
                    MDAMGC, MDENV, MDSTS, MDOSC, MDSRCF, MDSRCL, MDWKSR, MDWKSF, MDWKSL, MDARSF, MDARSL,
                    MDRCDA, MDCRT2, MDELSP, MDCRT#, MDPROD, MDPMDL )

                    ( Select 1, 1, 'DTA', 'LPU9999999', ODOBNM, ODOBAT, ODOBTP, ODOBTX, 10, 'GAMING_SR', 'GAMING_PRD', 'LIVE', 'RDY',
                    'SRCMBR', 'MFSLDSRC', 'MFPSGAM', 'LPU9999999', 'QPRDSRC', 'MFPSGAM', 'MFSLDSRC', 'MFPSGAM',
                    '*OPEN', '#DSLD00002', 1, 1, 'GAMING', 'SLOTDPATCH'
                    from qtemp.pryvlobjs1
                    where substr(odobnm,1,1) in ('S','Y') and ODOBAT = 'PF' );

                    Hope this helps someone someday.

                    Comment

                    Working...
                    X