Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 insert statement

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
star_dhruv2000

New User


Joined: 03 Nov 2006
Posts: 87
Location: Plymouth, MN USA

PostPosted: Tue Nov 28, 2006 6:51 pm    Post subject: DB2 insert statement
Reply with quote

Hello people I need to enter more then one value in table but by using following command I can only enter one record viz

Code:
insert into table-name values()


I have also tried to use this but it dosent work for DB2

Code:
insert into table-name values(),(),().....



So plz tell me a suitable answer for this.

Thanks and regards
Back to top
View user's profile Send private message

rambabu
Currently Banned

New User


Joined: 18 Apr 2005
Posts: 67

PostPosted: Tue Nov 28, 2006 7:53 pm    Post subject: Re: DB2 insert statement
Reply with quote

Using insert-select stmt it insert multiple rows

INSERT INTO tbl_temp2 (fld_id)
SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
Back to top
View user's profile Send private message
shrivatsa
Warnings : 1

Active User


Joined: 17 Mar 2006
Posts: 171
Location: Bangalore

PostPosted: Tue Nov 28, 2006 10:50 pm    Post subject: Re: DB2 insert statement
Reply with quote

Yes Rambabu you are correct

The another way is like this

EXEC SQL
INSERT INTO <tablename>
(columnname1, columnname2, columnname3)
VALUES (:hostvariable1, :hostvariable2, :hostvariable3 :hostvariable4)
FOR :hostvariable ROWS
END-EXEC.

this insert the number of rows that are specified in the bolded host variable.

Thanks
Shri
Back to top
View user's profile Send private message
MFRASHEED

Active User


Joined: 14 Jun 2005
Posts: 186
Location: USA

PostPosted: Tue Nov 28, 2006 11:30 pm    Post subject: Re: DB2 insert statement
Reply with quote

If you have large number of rows to be inserted, another option would be to consider LOAD, read more about it in manual:

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNUGH11/2.12?SHELF=DSNSHHA1&DT=20010719144747
Back to top
View user's profile Send private message
star_dhruv2000

New User


Joined: 03 Nov 2006
Posts: 87
Location: Plymouth, MN USA

PostPosted: Wed Nov 29, 2006 11:48 am    Post subject:
Reply with quote

I want to directly put in values is there anything except LOAD command or can anyone provide me exact syntax for LOAD.

Thanks and regards[/code]
Back to top
View user's profile Send private message
MFRASHEED

Active User


Joined: 14 Jun 2005
Posts: 186
Location: USA

PostPosted: Wed Nov 29, 2006 8:59 pm    Post subject: Re: DB2 insert statement
Reply with quote

This link i provided you has SYNTAX (infact job step) for table LOAD, review '2.12.5 Sample control statement'.

If you want to insert values interactively and you have access try these couple of ways:

Hopefully table is small and you were trying to do this in a test environment.


1. QMF:
On QMF command line enter

Code:


edit QUALIFIER.TABLENAME



And you will get a screen where you can interactively insert values.

2. PLATINUM:

From PLATINUM Main menu select option '3' (RC/UPDATE)

Code:


----  r11    ------- Unicenter DB2 Products Main Menu  ------ 11-29-2006 0
OPTION ===>                                                   SCROLL ===>
                                                                         
DB2 SSID ==> DB2S LOCATION ==> LOCAL                 DB2 VERSION : V
ACM      ==> OFF  ACMID    ==> XXXX     SQLID ==> XXXX                   
                                                                         
<-> Backup and Recovery                 <-> Report Facility               
    _ LA Log Analyzer                       _ R  Report Facility Menu     
    _ MM Merge/Modify                                                     
    _ Z  Recovery Analyzer              <-> Utilities                     
                                            _ U  DB2 Object Manager       
<-> Database Administration                                               
    _ PX Partition Expert               <-> Value Pack                   
    _ 1  RC/Query                           _ B  Batch Processor         
    _ 2  RC/Migrator                        _ C  DB2 Command Processor   
    X 3  RC/Update                          _ I  Interactive SQL         
    _ 4  RC/Secure                          _ M  Alt. Catalog Mapping     
    _ 5  RC/Extract                         _ TT Thread Term/Dynam DSNZPAR
                                            _ Y  Utility Manager         
<-> General Facilities                      _ 6  RI Manager               
    _ A  Product Authorizations                                           
    _ DF Dataset Facility                                                 
-------------  (C) 2004 Computer Associates International, Inc. ----------


Next screen will be RC/Update Main Menu, fill in

Option as 'E' (Edit) or 'FE' (Fast Edit), enter table name in 'Item Name' and Qualifier in 'Creator'

Code:

Option    ===> FE                 Object   ===> T        Mode  ===> O ONLINE
Item Name ===> TABLNAME           Creator  ===>   QUAL       Where ===> N       
----------------------------------------------------------------------- XXXX Location  ===> LOCAL              DB2 SSID ===> DB2S     DB2 Version: V7R1M0
                                                                             
    Object Options             Objects  (for use with Object Options)       
      A  - Alter                 SG - Storage Group     T - Table           
      C  - Create                DB - Data Base         I - Index           
      D  - Drop                  TS - Table Space       V - View             
      T  - Template              TG - Trigger           S - Synonym         
      R  - Drop Recovery         RI - Ref.Integrity     A - Alias           
                                                        J - Join/Edit       
                                                                             
    Operation Control          Table Data Options                           
      L  - Log Display           B  - Browse            FB - Fast Browse     
      P  - Profile               E  - Edit              FE - Fast Edit       
      BP - Batch Processor       CO - Copy              DC - Data Compare   
      IS - Interactive SQL       RB - RI Browse         RE - RI Edit         
      TU - Tutorial                                                         
-----------   (C) 2004 Computer Associates International, Inc.     ----------


This will display you all the existing rows in table, just repeat the line and change values to what you want.

3.And lastly if with good old INSERT statement you will have to just repeat the statement.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts COBOL DB2 - CALL statement - high CPU... TS70363 DB2 15 Sun Sep 11, 2016 6:07 am
No new posts Insert Lines in JCL with Rexx after a... Willy Jensen CLIST & REXX 3 Tue Aug 30, 2016 4:18 pm
No new posts How to insert a lengthy string havin... vidyaa DB2 7 Thu Aug 25, 2016 5:20 pm
No new posts Converting NULL column into NOT NULL ... Raghu navaikulam DB2 5 Sat Aug 06, 2016 3:45 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us