Create Table statement

Hi,

I brand new to Sybase and the specific flavour of SQL.

What is the syntax for creating a temporary table from a select statement. That is a temp table is created already populated with rows from a select statement...?

Thanks Ian
LVL 1
ISCAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shanmuga SundaramDirector of Software EngineeringCommented:
I am not sure whether this helps. but give a try

select * into #temptablename from yourtablename


Note "#" should be included to create a temp table and this will be deleted.
0
grant300Commented:
There are two kinds of temporary tables, private and public.

Private temporary tables always have a '#' sign in the front of the name.  They can be created with as shsunder stated using the select ... into #tblname from... syntax or explicitly with a create table #tblname.... statement.  They live in tempdb.  Private temp tables live for the shorter of the session (connection) or the stored procedure in which they were created and are dropped automatically.  Private temp tables are not visible to or accessible in any way by any other session on the server.  Not even DBO or SA can get to the table contents.

Public temporary tables are regular tables created explicitly in the tempdb, e.g. create table tempdb.dbo.tablename or select... into tempdb..tablename.  They live until the server is restarted.  This is because tempdb is recreated every time the server is starated.  Public temporary tables are visible and accessible using the same access control mechanisms as a permanent table.

BTW, if you just wanat to create a temp table from a regular table but don't wish to move any data, you can use the SELECT ... INTO #tblname FROM table_name WHERE 1 = 2.  This uses the source table and select list as the template when creating the #tblname but does not move any rows since the SARG will obviously never evalutate to true.

Regards,
Bill
0
IncisiveOneCommented:
shasunder has answered the posted question.


Grant300

I do not agree with your nomenclature.

1  Whether temp tables are "private" or "public" or not, is a matter of whether permissions are granted by the creator, nothing else.
2  The correct name for invisible or implicit temp tables, what you call "private", is WorkTables.  These are created/dropped automatically by ASE.
3  There are two types of temp tables:
" those create with a "#mytable" which carry certain implications (the actual physical name is something like
tempdb.user.mytable_875275378217
etc and therefore is not available to other users)
" and those created with "create table tempdb..tablename".  Generally that implies (but does not require) some form of sharing between processes/users, permissions, etc.
4  It is good practice to do one's own housekeeping, and truncate and drop temp tables within the code segment that created them.

Cheers

0
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

grant300Commented:
Work tables are something different yet; they do not have metadata in the system tables at all.

The #mytable temp tables are, among other things, private by design.  You cannot grant access to them to another user, or even another process, even if it is the same login.  The hash of the name to make them unique is done in part because the metadata for a #temptable is actually present in the tempdb system tables.  Even if you go and dig out the hashed name in tempdb, you still cannot access them.

Using #temptables and allowing Sybase to clean them up is perfectly acceptable in the context of stored procedures.  The only times you need to worry about explicitly droping them (the truncate is unncessary) is if you have a long running multi-step stored procedure and your temp tables are large enough that you need to worry about freeing up space in tempdb OR when you create a #temptable at the connection level and are finished with it.  In that case, you definetly want to drop it when you are done with it so that it does not hang around for the entire life of the session.

Regards,
Bill
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IncisiveOneCommented:
Ok, so now I understand your nomenclature.

I did not say truncate is "necessary".  I said it was good practice.  It is a well-known performance trick to reduce logging.

It is always good practice, regardless of table size, to keep the server trim and drop your #tables rather than leave it for the server to cleanup.  tempdb..tables of course are of course public by design, so keeping it trim means deleting your portion of it.  Failure to adhere to good practice simply means your code is selfish and not conducive to concurrent usage.

Cheers

0
grant300Commented:
A DROP TABLE does an implicit TRUNCATE before it removes the metadata from the system tables.  Processing a second SQL statement adds a small but unnecessary amount of overhead to the process.

Agreed.  Too many developers view the entire world as residing in there PC and fail to take into account that, in a server environment, you may be affecting anywhere from a few to thousands of other users.  We often fail to appreciate the disciplines that the mainframe folks developed that allows them to run banks and airlines without having to reboot when an error occurs ;-)

Regards,
Bill
0
IncisiveOneCommented:
> TRUNCATE before it removes the metadata from the system tables. <

No, it doesn't.

> Agreed.  Too many developers view the entire world as ... <

Agreed.  Too many chefs, bank managers, solicitors and accountants think themselves "developers".
0
grant300Commented:
I'm curious what you believe a Truncate operation does that is not also accomplished with a Drop Table?

Regards,
Bill
0
IncisiveOneCommented:
It is a not commonly known performance trick to truncate any table (particularly a temp table) before dropping the table.  Have a look at what gets written to the log.
0
grant300Commented:
I thought you might be hinting at the fact that a DROP TABLE and be rolled back but a TRUNCATE cannot.  My guess is that you are assuming that the DROP TABLE will essentially log a delete for every row in the table so that it can do the rollback.

I did some testing using ASE 15.0.2.  I created and populated a table with a SELECT/INTO FROM syscolumns and then tried a straight DROP TABLE and a TRUNCATE before a DROP TABLE.  I also tried putting the DROP TABLE within a transaction and both committing it and rolling it back.

The fewest number of log entries (73) was a straight DROP TABLE, with or without being in the context of a transaction.

Doing a truncate first added 5 log entries for a total of 78.

A DROP with a ROLLBACK resulted in 143 log entries and, again adding the TRUNCATE increased that by 5 to 148.

I ran these test with 556 rows in the table and about 10,000 rows in the table and there was no difference.

Clearly, doing a DROP TABLE in the context of a transaction does NOT log all of the rows in the table; only the meta data (system table) rows that will be removed are logged.  Since that includes all the space allocation information, there is no need to log all of the table contents; a rollback simply keeps the status quo.

As I said, the only thing you are buying with a Truncate prior to a Drop is the extra command processing and a few more log entries.

Regards,
Bill
0
ISCAuthor Commented:
Super, clear and accurate..
0
IncisiveOneCommented:
Ok, the point has been proved.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.