How to: Join two table structures into another table 1

[keepingbusy]

Hi

How do you join the structures of two tables (say mytable1 and mytable2) into a new table?

Thank you.

 

[mm0000]

select table1.field1, table1.field2, ...., table2.field1, table2.field2,..... where table1.field1 = '@@@' into table table3

 

[AndyKr]

>>How do you join the structures of two tables (say mytable1 and mytable2) into a new table?

You can do this in a number of ways, depending on the situation. Probably the simplest is to use SQL to create a new table directly from the contents of existing tables:

SELECT [field_list] FROM [tables] WHERE [conditions] INTO TABLE [new_table]

Alternatively you could use the COPY STRUCTURE EXTENDED command to get the structures of your tables into tables and then create a new structure definiton file from them
Alternatively you could use AFIELDS() to get the table structures and then create a new array and use that to generate a new table.
It really depends on what your requirements are...

----
Andy Kramek
Visual FoxPro MVP

 

[keepingbusy]

mm0000 and AndyKr

I did note from the help file something similar to what you have mentioned. There are about 40 fields in one table and about 50 in the other so I dont think there is an easy way to do this other than what you have mentioned about:

select table1.field1, table1.field2, ...., table2.field1, table2.field2,..... etc etc

Many thanks to both

 

[Mike Lewis]

here are about 40 fields in one table and about 50 in the other

To avoid having to list every single field in your SELECT, you could do this:

SELECT Tab1.*, Tab2.* FROM Tab1, Tab2 ;
  WHERE .F. ;
  INTO TABLE Tab3

Provided the total number of fields doesn't exceed 256, that should work.

The only problem is that, if any of the field names are the same in both tables, VFP will append _a or _b to them. But then, you would need a way of resolving duplicated field names in any case.

Mike





__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[keepingbusy]

Mike

I think your suggestion could be what I'm looking for. I'll try it out and post back soon.
(Hope all is well)

Thank you
Lee

 

[keepingbusy]

Mike

SELECT Tab1.*, Tab2.* FROM Tab1, Tab2 ;
  WHERE .F. ;
  INTO TABLE Tab3

Please could you explain the concept regarding the above?

If my two tables are called orders1 and orders2 and the third table is allorders, my understanding of it is something like...

SELECT ORDER1 FROM ORDER2 WHERE ;
  (not sure what condition goes here) INTO TABLE ALLORDERS

I take your point about:

Provided the total number of fields doesn't exceed 256, that should work.

This wont be an issue as the first table has about 40 fields and the second about 50.

Some guidance would be appreciated.

Thank you
Lee

 

[Mike Lewis]

Lee,

This is the code you need:

SELECT ORDER1.*, ORDER2.* FROM ORDER1, ORDER2 WHERE .F. ;
  INTO TABLE ALLORDERS

The .* tells it to select all fields in the respective tables (to save you having to type 90 field names).

FROM ORDER1, ORDER2 You need to mention both input tables here, otherwise you'll get an error.

WHERE .F. This tells it not to select any records -- since you only want the structure, not the actual data. Without this clause, you would get every record from order1 combined with every record from order2 -- a potentially huge output table.

Hope this makes sense. Come back if you need any further clarification.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[keepingbusy]

Mike

That worked perfectly and I'm grateful to you for explaining the coding which allows a better understanding.

Star for you!

Kind regards
Lee

 

[Mike Lewis]

Lee,

Delighted to have been able to help.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk