Temporary TablesBy Bill Graziano on 17 January 2001 | Tags: Application Design , Table Design Sophie writes "Can you use a Stored Procedure to open a table and copy data to a sort of virtual table (or a records set) so that you can change the values with and not affect the actual data in the actual table. And then return the results of the virtual table? Thanks!" This article covers temporary tables and tables variables and is updated for SQL Server 2005. I love questions like this. This question is just a perfect lead in to discuss temporary tables. Here I am struggling to find a topic to write about and I get this wonderful question. Thank you very much Sophie. Temporary TablesThe simple answer is yes you can. Let look at a simple CREATE TABLE statement: CREATE TABLE #Yaks ( YakID int, YakName char(30) ) You'll notice I prefixed the table with a pound sign (#). This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When I close this session, the table will be automatically dropped. You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key constraints on a temporary table. The others are covered in Books Online. Temporary tables are created in CREATE TABLE #Yaks ( YakID int, YakName char(30) ) select name from tempdb..sysobjects where name like '#yak%' drop table #yaks You'll get something like this: name ------------------------------------------------------------------------------------ #Yaks_________________________ . . . ___________________________________00000000001D (1 row(s) affected) except that I took about fifty underscores out to make it readable. SQL Server stores the object with a some type of unique number appended on the end of the name. It does all this for you automatically. You just have to refer to #Yaks in your code. If two different users both create a #Yaks table each will have their own copy of it. The exact same code will run properly on both connections. Any temporary table created inside a stored procedure is automatically dropped when the stored procedure finishes executing. If stored procedure A creates a temporary table and calls stored procedure B, then B will be able to use the temporary table that A created. It's generally considered good coding practice to explicitly drop every temporary table you create. If you are running scripts through SQL Server Management Studio or Query Analyzer the temporary tables are kept until you explicitly drop them or until you close the session. Now let's get back to your question. The best way to use a temporary table is to create it and then fill it with data. This goes something like this: CREATE TABLE #TibetanYaks( YakID int, YakName char(30) ) INSERT INTO #TibetanYaks (YakID, YakName) SELECT YakID, YakName FROM dbo.Yaks WHERE YakType = 'Tibetan' -- Do some stuff with the table drop table #TibetanYaks Obviously, this DBA knows their yaks as they're selecting the famed Tibetan yaks, the Cadillac of yaks. Temporary tables are usually pretty quick. Since you are creating and deleting them on the fly, they are usually only cached in memory. Table VariablesIf you are using SQL Server 2000 or higher, you can take advantage of the new TABLE variable type. These are similar to temporary tables except with more flexibility and they always stay in memory. The code above using a table variable might look like this: DECLARE @TibetanYaks TABLE ( YakID int, YakName char(30) ) INSERT INTO @TibetanYaks (YakID, YakName) SELECT YakID, YakName FROM dbo.Yaks WHERE YakType = 'Tibetan' -- Do some stuff with the table Table variables don't need to be dropped when you are done with them. Which to Use
Answering the QuestionAnd all this brings us back to your question. The final answer to your question might look something like this: DECLARE @TibetanYaks TABLE ( YakID int, YakName char(30) ) INSERT INTO @TibetanYaks (YakID, YakName) SELECT YakID, YakName FROM dbo.Yaks WHERE YakType = 'Tibetan' UPDATE @TibetanYaks SET YakName = UPPER(YakName) SELECT * FROM @TibetanYaks Global Temporary TablesYou can also create global temporary tables. These are
named with two pound signs. For example, SummaryThat shows you an example of creating a temporary table, modifying it, and returning the values to the calling program. I hope this gives you what you were looking for.
|
- Advertisement - |