View previous topic :: View next topic
|
Author |
Message |
jamshirqureshi
New User
Joined: 13 Aug 2006 Posts: 20 Location: mumbai
|
|
|
|
Hello Friends,
Has anyone tried to insert multiple SQL rows from SPUFI into DB2.
If its insert please give me the syntax.
thx
Jamz |
|
Back to top |
|
|
somnath
New User
Joined: 21 Mar 2005 Posts: 18 Location: India
|
|
|
|
I don't think that you can insert multiple rows using spufi. You can do so by using Cobol-DB2 program or you can use multiple insert statement in spufi. |
|
Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005 Posts: 1208 Location: Bangalore,India
|
|
|
|
Hi jamz,
What do u mean by insert multiple rows from SPUFI?
Do u want to know whether we can use Insert ...select? |
|
Back to top |
|
|
jamshirqureshi
New User
Joined: 13 Aug 2006 Posts: 20 Location: mumbai
|
|
|
|
I mean i want to insert Mulptile rows using single ISNERT statement through SPUFI
Is it possible in SPUFI , i know we can do this using cursor.
And i dont want multiple insert statment also. i want it in single statment
example
Insert into Student_table
(stud_name, Roll_no, Mark)
values ('Ram', 101, 255),
('Shyam' 102, 350)
so like this but if i run this query in spufi it ll show the -104 error which illegl ',' symbol.
which is values ('Ram', 101, 255), <=== here
so what i wanted is , is there any other way to continue to insert mupltiple rows using single INSERT statment through SPUFI |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Intead of this why dont you try to make use of LOAD utility.It will help you insert more number of rows. |
|
Back to top |
|
|
jamshirqureshi
New User
Joined: 13 Aug 2006 Posts: 20 Location: mumbai
|
|
|
|
Hi,
I know LOAD utility, but i wanted to know that we can do like this ? |
|
Back to top |
|
|
jamshirqureshi
New User
Joined: 13 Aug 2006 Posts: 20 Location: mumbai
|
|
|
|
But for LOAD utility we need Multiple INSERT statement |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
I guess you have got the wrong conception about LOAD utility please go through the manuals it is very clear............. |
|
Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006 Posts: 1049 Location: Richmond, Virginia
|
|
|
|
I do not think you can use one INSERT for multiple rows. I tried this, and then began using LOAD. LOAD is very nice, well documented, and easy to use.
I recommend you test first with very small files to achieve a comfort level. I use a personal sandbox table with different kinds of columns, nullable and not, just for testing.
Note that UNLOAD creates LOAD control cards that correspond to the UNLOADed data, so you can UNLOAD first, then format your data-to-be-loaded similarly and use the generated LOAD controls.
Caution: I believe that the LOAD replace optiion works by tablespace, not table, so if you have tables sharing a tablespace, watch out!
Also watch for location/value of null indicators. |
|
Back to top |
|
|
prasadvrk
Active User
Joined: 31 May 2006 Posts: 200 Location: Netherlands
|
|
|
|
Jamshir,
If you are desparate about using SPUFI alone, you can have as many INSERT statements as you want in the same SPUFI file separated by ;
eg.
Insert into Student_table
(stud_name, Roll_no, Mark)
values ('Ram', 101, 255);
Insert into Student_table
(stud_name, Roll_no, Mark)
('Shyam' 102, 350) ;
and run them all at once, SPUFI can run multiple queries. I hope this answers your problem without using the LOAD but always LOAD is advisable. |
|
Back to top |
|
|
vini_srcna
Active User
Joined: 26 May 2005 Posts: 178 Location: Copenhagen, Denmark
|
|
|
|
Hi Guys,
DB2 Manuals says we can insert multiple rows in a single insert statement. As a matter of fact this syntax works only on DB2 LUW (Linux, Unix, Windows). It is not supported in DB2 V 7.1 for Z/os atleast. I didnt get a chance to check it on V8 and Viper yet. If am right it is not supported in V8 too. |
|
Back to top |
|
|
wanderer
Active User
Joined: 05 Feb 2007 Posts: 199 Location: Sri Lanka
|
|
|
|
V8 in z/OS supports multiple row inserts but only through an application program where you can use the name of an array and all are inserted in one go. I don't think it is possible using spufi. |
|
Back to top |
|
|
vini_srcna
Active User
Joined: 26 May 2005 Posts: 178 Location: Copenhagen, Denmark
|
|
|
|
I shall agree. V8 Came with Multi row fetch, Insert & Update. It would happen through application program and only in V8 after. |
|
Back to top |
|
|
girias
New User
Joined: 09 Feb 2007 Posts: 26 Location: Chennai
|
|
|
|
Hello All, Whether DB2 V9 supports this? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
Whether DB2 V9 supports this |
Supports what. . .?
You have replied to a topic that has been inactive for almost 2 years.
Suggest you start a new topic for your question and make sure that your question is completely/clearly presented. |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
WOW GUYZ,
2 YEAR old discussion brought back to live. But still no answer ?
I SEAROOGLED & came across an interesting articleon this but on a different Database.
I checked something like this, it worked in SPUFI. Iam on V8.
Code: |
insert into bobt1.country
select 301,'INDIA','PARRYS Station' from sysibm.sysdummy1
union all
select 302,'INDIA','AMAMJIkarai' from sysibm.sysdummy1; |
Answers will give you pleasant sleep,
Sushanth Bobby |
|
Back to top |
|
|
girias
New User
Joined: 09 Feb 2007 Posts: 26 Location: Chennai
|
|
|
|
Hi Sushanth,
WoW!!! it worked. gr8!!... Thanks a lot.... |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Happy to help! & Happy to learn!
Let this post R.I.P,
Sushanth Bobby |
|
Back to top |
|
|
|