I'm brand new to the forum...searching for some help on Sybase. Pleas
bear with me. I'm normally doing Oracle and MSSQL7/2K. So I'm used t
being able to create a table on the fly as a SELECT...INTO or CREAT
TABLE AS SELECT statements. It sort of amazes me that you have t
actually sit down and write a create table script or go through th
SybCentral GUI which I consider balky. I'm usually working in database
that are part of delivered apps, and just extending the functionality


In the post "Create table as select"
http://tinyurl.com/2nj94
Tom mentioned that you can do a Select Into if the option is turned o
for the database.

I'm running ASA 8 Ver 8.0.1.3041. I can't find the option.

Or in the alternative is there anyway to do a select that will create
script for creating a table?

TIA for any assistance


-
jimpe
-----------------------------------------------------------------------
Posted via http://www.webservertalk.co
-----------------------------------------------------------------------
View this thread: http://www.webservertalk.com/message190125.htm

You use the select a, b, c, etc into tablename
from table_in
where x = ?

If you wish to have an empty table use "where 1 = 2"

You can see online manuals at http://sybooks.sybase.com/as.html

From the ASE Reference manual on select :

Example 5
Specifies the locking scheme and the reserve page gap for select into:
select title_id, title, price
into bus_titles
lock datarows with reservepagegap = 10
from titles
where type = "business"
"jimpen" <jimpen.1536h5 (AT) mail (DOT) webservertalk.com> wrote

When I run the query:

-SELECT *
INTO TEST_TABLE
FROM LOAN_APPLICATION
WHERE APPLICATION_NO >
(SELECT MAX(APPLICATION_NO)-5
FROM LOAN_APPLICATION)-

I get the ISQL Error:
-ASA Error -260: Variable 'TEST_TABLE' not found-

Instead of beating my head against the wall over this (I thing there i
a bug in ASA8) I ended up figuring a way around it. I built a functio
in MS Access to read from a view and build the table create script
I've attached the function in a text file.

To use it
- Put the function in an Access module
- Create a view in the Sybase DB
- Create an ODBC link to the view from Access
- In the debug window run WriteSybaseTableStatement("ViewName") It will writ
the results to the debug window.


Of course it won't have primary keys and table sizing info, but it wil
give you a basic layout of the table. This is just a modification of
function for extracting the table design of an Access table. An
actually could be used to read Access table design for building into
SyBase DB. Note that there is a 30 character limit on column names
That is easy to change in the function by just upping the Space(
value.

Willie Kraatz wrote:
+----------------------------------------------------------------

-
jimpe
-----------------------------------------------------------------------
Posted via http://www.webservertalk.co
-----------------------------------------------------------------------
View this thread: http://www.webservertalk.com/message190125.htm