FoxPro: Append arbitrary number of views into a single view or table

Question

I have several views which I need to combine into a single view or table. The views all have the same structure and are titled view_1, ..., view_x.

I've had the best luck with the following. I can get the total number of views in the database, so I have just used 12 here for simplicity. The code creates a new view called test and populates it with the contents of view_1 as expected. No errors are thrown when executed. In the status bar, I see that it has processed XXXX records. This corresponds to the last view (view_12). However, test only contains the contents of view_1.

create sql view test as;
select * ;
from view_1 

for i = 2 to 12
    insert into test ;
    select * ;
    from view_&i. 
endfor

Am I missing something simple or is there a better way to do this?

Answer 1

You can't really treat views like tables unfortunately - they are just stored SQL statements. So to create a new view with the results of other views you essentially need to build a new SQL statement based on the SQL statements of the other views.

Note this assumes that the schema for each smaller view is the same, as you said :

Close All
Clear All
Clear

Open Database mydatabase Excl

lnViews = Adbobjects(laViews, "VIEW")

If Ascan(laViews, "vcombined") > 0
    Delete View vcombined
Endif

lcSQL = ""

* -- You'll probably find out the view count and put it here.
For lnView = 1 to 3

    lcView = "view_" + Transform(lnView)
    lcSQL = lcSQL + Iif(lnView > 1, "union ", "") + DBGetProp(lcView, "View", "SQL")

Endfor  

Create SQL View vcombined as &lcSQL