DB2 insert statement

star_dhruv2000 · Posted: Tue Nov 28, 2006 6:51 pm

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

rambabu · New User Joined: 18 Apr 2005 Posts: 67

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;

shrivatsa · Posted: Tue Nov 28, 2006 10:50 pm

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

MFRASHEED · Active User Joined: 14 Jun 2005 Posts: 186 Location: USA

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

publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNUGH11/2.12?SHELF=DSNSHHA1&DT=20010719144747

star_dhruv2000 · Posted: Wed Nov 29, 2006 11:48 am

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]

MFRASHEED · Active User Joined: 14 Jun 2005 Posts: 186 Location: USA

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