how to insert into a table with another tables data

GANAPATH · New User Joined: 06 Mar 2006 Posts: 35

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

DavidatK · Posted: Fri Jan 12, 2007 2:08 am

How does TABLE A and TABLE B relate to each other.

RamsIBM · New User Joined: 08 Jan 2007 Posts: 53 Location: Chennai

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

mkk157 · Active User Joined: 17 May 2006 Posts: 310

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?

RamsIBM · New User Joined: 08 Jan 2007 Posts: 53 Location: Chennai

Kranthi Kumar Maddineni,

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

Let Ganapath come up with his requirement........

GANAPATH · New User Joined: 06 Mar 2006 Posts: 35

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

dick scherrer · Posted: Sat Jan 13, 2007 1:15 am

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).

DavidatK · Posted: Sat Jan 13, 2007 4:12 am

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.

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.

dick scherrer · Posted: Sat Jan 13, 2007 4:41 am

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?

dick scherrer · Posted: Sat Jan 13, 2007 4:42 am

Hello,

Of course, if you actually want a cartisian product, that would be easy to arrange as Dave pointed out.

DavidatK · Posted: Sat Jan 13, 2007 6:01 am

Dick,

Even if they are related by row position, I just wanted Ganapth to recognize this relationship. It gives us something to work with.