?
Solved

Create Table statement

Posted on 2008-11-07
12
Medium Priority
?
1,401 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:ISC
12 Comments
 
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 22905926
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
 
LVL 19

Expert Comment

by:grant300
ID: 22908241
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
 
LVL 6

Expert Comment

by:IncisiveOne
ID: 22914212
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
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 
LVL 19

Accepted Solution

by:
grant300 earned 2000 total points
ID: 22916845
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
 
LVL 6

Expert Comment

by:IncisiveOne
ID: 22918974
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
 
LVL 19

Expert Comment

by:grant300
ID: 22923610
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
 
LVL 6

Expert Comment

by:IncisiveOne
ID: 22927988
> 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
 
LVL 19

Expert Comment

by:grant300
ID: 22933176
I'm curious what you believe a Truncate operation does that is not also accomplished with a Drop Table?

Regards,
Bill
0
 
LVL 6

Expert Comment

by:IncisiveOne
ID: 23000850
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
 
LVL 19

Expert Comment

by:grant300
ID: 23007195
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
 
LVL 1

Author Closing Comment

by:ISC
ID: 31514423
Super, clear and accurate..
0
 
LVL 6

Expert Comment

by:IncisiveOne
ID: 23038739
Ok, the point has been proved.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Moore’s Law has proven itself time and time again since it was first introduced. So what’s next? Will Moore’s law continue to remain relevant, or will new technology take over and bring us the next big advancement in computing?
Social messanging services like WhatsApp and Facebook can help businesses in ways that many owners don't even imagine, giving new opportunities to connect with customers. Discover some of the most innovative things they can do for your company.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses
Course of the Month15 days, 12 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question