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
 

 

how to insert into a table with another tables data

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

New User


Joined: 06 Mar 2006
Posts: 35

PostPosted: Fri Jan 12, 2007 1:39 am    Post subject: how to insert into a table with another tables data
Reply with quote

Hi,

i have

TABLE A with COL1 ,COL2 and COL3
TABLE B with COL4, COL5 AND COL6

I WANT TO insert into TABLE C WITH values of

COL1,COL2,COL5

how can i do this.

Thanks,
Ganapath
Back to top
View user's profile Send private message

DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Fri Jan 12, 2007 2:08 am    Post subject: Re: how to insert into a table with another tables data
Reply with quote

How does TABLE A and TABLE B relate to each other.
Back to top
View user's profile Send private message
RamsIBM

New User


Joined: 08 Jan 2007
Posts: 53
Location: Chennai

PostPosted: Fri Jan 12, 2007 1:28 pm    Post subject:
Reply with quote

INSERT INTO TABLEC SELECT X.COL1,X.COL2,Y.COL5 FROM TABLEA X,TABLEB Y WHERE X.<KEY FIELD> = Y.<KEY.FIELD>

Key Field is nothing relation field
Back to top
View user's profile Send private message
mkk157

Active User


Joined: 17 May 2006
Posts: 310

PostPosted: Fri Jan 12, 2007 3:14 pm    Post subject:
Reply with quote

Hi RamsIBM,



If there is a relation between the tables I agree with u. But Just imagine that there is no relation between the two tables. In that situation How can I write the query?
Back to top
View user's profile Send private message
RamsIBM

New User


Joined: 08 Jan 2007
Posts: 53
Location: Chennai

PostPosted: Fri Jan 12, 2007 4:23 pm    Post subject:
Reply with quote

Kranthi Kumar Maddineni,

Yeap, we have the way for that also..... icon_smile.gif

Let Ganapath come up with his requirement........
Back to top
View user's profile Send private message
GANAPATH

New User


Joined: 06 Mar 2006
Posts: 35

PostPosted: Fri Jan 12, 2007 6:47 pm    Post subject: Re: how to insert into a table with another tables data
Reply with quote

Thanks for the answer.Its good to know different things, but none of my tables are related, all the columns that i am choosing to write to TABLE C are different Datatypes and the KEY cols of A and B are also of different datatypes.

Thanks in advance.

Ganapth
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: Sat Jan 13, 2007 1:15 am    Post subject:
Reply with quote

Hello,

Then it would most likely be easier to write COBOL code that reads a record from each table then writes the "new" data from the appropriate fields rather than trying to write a single sql statement that does it all.

Continue thru reading 1 record from each table and creating the new table until you have read all of the input (in one table (or the other) if they do not have the same number of rows).
Back to top
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Sat Jan 13, 2007 4:12 am    Post subject: Re: how to insert into a table with another tables data
Reply with quote

Ganapth,

The two tables MUST be related to each other in some way. Well, in fact they don?t, but you will get a cartesian product (the product of both tables). i.e. TABLE A has 2500 rows, TABLE B has 5500 rows, the cartesian product of these two table will be 13750000 rows. icon_eek.gif Even if you chose to use a COBOL program to populate TABLE C. How are you going to determine which row from TABLE B goes with which row from TABLE A?

If you can define this relationship, there is probable SQL to do it.
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: Sat Jan 13, 2007 4:41 am    Post subject:
Reply with quote

Hello,

If code is written to move thru BOTH tables 1 row at a time, there will be no cartisian product.

If the application has a process that ALWAYS adds 1 row to each table their relation may be the order the rows were added - even though no identifier was defined so they could easily be joined. Row 2326 in table a was added at the same time row 2326 was added to table b and that is the relationship.

Let's say that the design of these tables may be questionable.

If you declare a cursor for each table and then fetch a row from "a" and a row from "b" you'll have one of each and can issue an inseert with the selected info. One thought is how will table c be defined - will it not relate to either of the others?

When this is working, what kind of data requirement will it support?
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: Sat Jan 13, 2007 4:42 am    Post subject:
Reply with quote

Hello,

Of course, if you actually want a cartisian product, that would be easy to arrange as Dave pointed out.
Back to top
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Sat Jan 13, 2007 6:01 am    Post subject: Re: how to insert into a table with another tables data
Reply with quote

Dick,

Even if they are related by row position, I just wanted Ganapth to recognize this relationship. It gives us something to work with. icon_biggrin.gif
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 1 Mon Mar 27, 2017 9:58 pm
No new posts Export flat file data into excel sheet murali.andaluri DFSORT/ICETOOL 2 Mon Mar 20, 2017 5:39 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 SPUFI -- Joining 3 tables – data in... Sysaron DB2 2 Wed Mar 08, 2017 4:18 am
No new posts To know activities on db2 tables ashek15 DB2 4 Fri Mar 03, 2017 11:23 pm


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