Page 1 of 1
INSERT MORE THAN ONE ROWS IN SPUFI
Posted: Wed Jun 17, 2009 2:04 pm
by technokrat
HI,
can anyone help me out in inserting two rows at a time
i tried with the following syntax but it gave me error.
INSERT INTO EMPLOYEE(EMPNAME,SALARY,LOCATION)
VALUES('RAJU',8888,'BANGALORE'),
('JOHN',7777,'CHENNAI'),
('KEVIN',7676,'NEW YORK');
Posted: Wed Jun 17, 2009 2:21 pm
by Natarajan
Hi,
Syntax looks correct.
what error you are receiving?
Posted: Wed Jun 17, 2009 2:27 pm
by technokrat
SQLCODE = -104, ERROR: ILLEGAL SYMBOL ",".
Posted: Wed Jun 17, 2009 2:49 pm
by Natarajan
what is your db2 version.
Posted: Wed Jun 17, 2009 2:56 pm
by technokrat
its a 8.1 version
Posted: Wed Jun 17, 2009 3:00 pm
by dbzTHEdinosauer
you have to use UNION for multiple inserts when you are providing values.
because you can not have multiple values for an insert, in all db2 versions.
DB2 MULTI-ROW INSERT COBOL - DB2
Posted: Wed Jun 17, 2009 3:08 pm
by Natarajan
with DB2 version 8 multi-row insert will work.
Not sure whether it will work with spufi or not.
This DB2 feature work with any application program like COBOL.
This syntax may work on DB2 LUW for windows, unix and Linux.
Posted: Wed Jun 17, 2009 3:09 pm
by technokrat
Can u plz modify the above query for using UNION ???
Posted: Wed Jun 17, 2009 3:17 pm
by dbzTHEdinosauer
Can u plz modify the above query for using UNION ???
INSERT INTO EMPLOYEE(EMPNAME,SALARY,LOCATION)
VALUES('RAJU',8888,'BANGALORE')
UNION
INSERT INTO EMPLOYEE(EMPNAME,SALARY,LOCATION)
('JOHN',7777,'CHENNAI')
UNION
INSERT INTO EMPLOYEE(EMPNAME,SALARY,LOCATION)
('KEVIN',7676,'NEW YORK')
UNION
INSERT INTO EMPLOYEE(EMPNAME,SALARY,LOCATION)
('JOHN',7777,'CHENNAI')
UNION
INSERT INTO EMPLOYEE(EMPNAME,SALARY,LOCATION)
('KEVIN',7676,'NEW YORK');
with DB2 version 8 multi-row insert will work.
could you please provide an example?
Posted: Wed Jun 17, 2009 3:23 pm
by Natarajan
Hi Dick,
My statement has come from following ibm material on DB2.
http://publib.boulder.ibm.com/infocente ... g/fmlt.htm
DB2 MULTI-ROW INSERT
Posted: Wed Jun 17, 2009 3:26 pm
by Natarajan
Technokrat,
You can also use following sytax for multi-row insert from spufi.
It is very similar to what dick has suggested.
Code: Select all
INSERT INTO EMPLOYEE
SELECT 'RAJU',8888,'BANGALORE' from SYSIBM.SYSDUMMY1
UNION ALL
SELECT 'JOHN',7777,'CHENNAI' from SYSIBM.SYSDUMMY1
UNION ALL
SELECT 'KEVIN',7676,'NEW YORK' FROM SYSIBM.SYSDUMM1;
Posted: Wed Jun 17, 2009 4:27 pm
by dbzTHEdinosauer
Natarajan,
you are correct. I did not bother to read the manual until after I posted (and before your subsequent posts).
sorry, I will research before posting.
Posted: Wed Jul 29, 2009 5:51 pm
by RaviTejaG
Hi Natarajan,
Can you explain above query
SELECT 'RAJU',8888,'BANGALORE' from SYSIBM.SYSDUMMY1
why u are selecting the record from SYSIBM.SYSDUMMY1
and what will happen here , when we doing union all
Posted: Wed Jul 29, 2009 9:25 pm
by dbzTHEdinosauer
RaviTejaG,
you need to read a little bit. SYSIBM.SYSDUMMY1 is a table (provided by db2-ibm) that allows you to select something that does not exist in a table.
you can set :timestamp = current_timestamp
or you can
select current_timestamp into :timestamp from sysibm.sysdummy1