Creating a table from query results

[david7777777777]

I've got a query that lists all of the tables and column names in my Pervasive 2000i database. I'm now looking at the results of this query in the SQL Data Manager query grid window.

How do I get the results of this query into a table? Thanks.

 

[stefes007]

you can always do it thru sql:

create table yourtable as select table_name, column_name from all_tab_columns;

( adapt this with your sql order ).

Hope i could help you.

 

[david7777777777]

Thank you. But exactly which part(s) do I need to "adapt?"

 

[david7777777777]

Here's my query. It returns all of the table names and all of the column names in all the tables:

Select XF$Name As TableName, F1.XE$ID As FID, F1.XE$Name As FieldName, F2.XE$ID As NotNullable
FROM X$File
INNER JOIN X$Field F1 On
F1.XE$File = XF$ID
LEFT OUTER JOIN X$Field F2 On
F1.XE$ID = F2.XE$Offset And
F1.XE$File = F2.XE$File
WHERE F1.XE$Name Not Like 'NN_%' And
F1.XE$DataType <> 255
Order By XF$Name, F1.XE$Offset

**********************************************************

This is my attempt at creating the table based on what you gave me. I am calling the table tbl_Index2:

create table tbl_Index2 as
Select XF$Name As TableName, F1.XE$ID As FID, F1.XE$Name As FieldName, F2.XE$ID As NotNullable
FROM X$File
INNER JOIN X$Field F1 On
F1.XE$File = XF$ID
LEFT OUTER JOIN X$Field F2 On
F1.XE$ID = F2.XE$Offset And
F1.XE$File = F2.XE$File
WHERE F1.XE$Name Not Like 'NN_%' And
F1.XE$DataType <> 255
Order By XF$Name, F1.XE$Offset

********************************************************
After running the above command, I get this error:

ODBC Error: SQLSTATE = 37000, Native error code = 0
Syntax Error: create table tbl_Index2 as<< ??? >>

Select XF$Name As TableName, F1.XE$ID As FID, F1.XE$Name As FieldName, F2.XE$ID As NotNullable

FROM X$File

INNER JOIN X$Field F1 On

F1.XE$File = XF$ID

LEFT OUTER JO
Driver not capable.

*********************************************************

Any help is greatly appreciated.

 

[stefes007]

Hi,

sorry i forgot all_tab_columns is an oracle system table.
Well, maybe your ODBC driver doesn't support the create table command ( if i do well remember, ODBC is only for DML ( data manipulation langage ) and doesn't work with DDL ( data definition langage )).

You have to execute this query with a direct connection to your database, not with a connection thru ODBC.
Look for the other application you have installed on your client machine: you must have one which connect directly to the database, without using ODBC .

See you.

 

[mirtheil]

Stefes007,
The Pervasive ODBC driver does support DML and DDL. A Create Table can be issued using ODBC.

David7777777777,
You can't issue a Create Table based on a Select statement. Create Table doesn't support sub queries.
When you say that you'd like to get the result into a table, do you mean another table in the database? If so, create the table with the appropriate fields you want stored then issue an Insert with your select as the subquery. mirtheil@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[david7777777777]

Thanks mirtheil. I am performing all of this stuff directly on the database server, using the Pervasive SQL Control Center. So I'm assumingthat I am directly connected to my database and not going through an ODBC driver. Right?

I like learning this stuff, so can you give me a place where I can learn how to use this INSERT command without giving me the entire syntax? I'm finding it difficult to find any resources for this Pervasive SQL stuff. Thanks.

 

[mirtheil]

Actually, the PCC does use ODBC but the driver does go directly to the engine. In terms of learning the syntax, I would recommend the SQL Engine Reference guide that's installed as part of the Pervasive.SQL 2000 product. I would also suggest using SP4, if you're not already. SP4 reintroduced some syntax and makes some things a run a little better.
mirtheil@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician