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
 

 

Insert multiple rows with single SQL

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

New User


Joined: 15 Oct 2009
Posts: 23
Location: Pune

PostPosted: Wed Nov 24, 2010 11:57 pm    Post subject: Insert multiple rows with single SQL
Reply with quote

Hi All,

I have to Insert several row into a table as described below but
I dont want to use 100s of queries, how can I get this result in one insert sql?

INSERT INTO TABLE1
(
Col1,
Col2,
Col3,
...)
VALUES(
A1,B,C)

I have to insert 100 rows, B C remains constant for all the rows but A1 keep changing from A1 to A100.

example-
A1,B,C
A2,B,C
A3,B,C
.....
.....
A100,B,C


Thank you!
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Nov 25, 2010 12:40 am    Post subject:
Reply with quote

Hello,

Then put the data into a file and load it. . .
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 186
Location: chennai

PostPosted: Thu Nov 25, 2010 10:08 am    Post subject: Reply to: Insert multiple rows with single SQL
Reply with quote

Hi Deja

If you are using a program to insert 100 rows using a single INSERT query, then it is possible. In which way you want to execute-SPUFI/QMF/Program ?

Regards
Raghu
Back to top
View user's profile Send private message
Deja vu

New User


Joined: 15 Oct 2009
Posts: 23
Location: Pune

PostPosted: Thu Nov 25, 2010 10:45 am    Post subject: Re: Reply to: Insert multiple rows with single SQL
Reply with quote

Hi,

I'll test in SPUFi but in production I'll write a query job under IKJEFT01 as don't have update access on production.

Thanks!

Raghu navaikulam wrote:
Hi Deja

If you are using a program to insert 100 rows using a single INSERT query, then it is possible. In which way you want to execute-SPUFI/QMF/Program ?

Regards
Raghu
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 186
Location: chennai

PostPosted: Thu Nov 25, 2010 11:16 am    Post subject: Reply to: Insert multiple rows with single SQL
Reply with quote

Hi

In SPUFI/QMF and in JCL using IKJEFT01 using SQL as input, I think it is not possible. As per my knowledge using an INSERT query one can insert only one row at a time using SPUFI/QMF/IKJEFT01. Some one will help you in this regard.

Regards
Raghu
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Nov 25, 2010 11:24 am    Post subject:
Reply with quote

Hello,

Suggest concentrating on getting the job done rather than trying to make some particular "solution" fit. . .

To repeat:
Quote:
Then put the data into a file and load it. . .


Why would a multiple insert query be preferred over the load?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Thu Nov 25, 2010 3:56 pm    Post subject: Reply to: Insert multiple rows with single SQL
Reply with quote

you'll need a table with at least 100 rows and a column to sort it on(I'll use sysibm.systables and obid as an example)

Code:
insert into T1
select 'A' !! char(row_number() over (order by obid))
,'B','C'
from sysibm.systables
fetch first 100 rows only
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 Data replication from multiple Db2 ta... kishpra DB2 5 Mon Mar 27, 2017 9:58 pm
No new posts Splitting of single report to seperat... abdulrafi TSO/ISPF 4 Mon Mar 27, 2017 3:59 pm
No new posts Unable to catalog a gdg dataset resid... Shovan JCL & VSAM 7 Fri Mar 24, 2017 2:24 pm
No new posts Append data from two files into a sin... Praveen04 DFSORT/ICETOOL 5 Thu Mar 16, 2017 7:29 pm
No new posts IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm


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