You may think that you don’t need a temporary table. But if you’ve ever retrieved data from your database or retrieved data from a table and put it in a list of some sort simply to process it further with your code, you need to learn about temporary tables.
I’ve also needed to use Temporary Tables to simulate arrays in my stored procedures. You can pass in a comma delimited list and then have your stored procedure process the list into a temporary table, making the information easier to evaluate.
In the first case, we reduce the amount of data that needs to pass between our database and our code. In the second case, there aren’t any other options to achieve what it is we want to do.
Creating a temporary table looks very similar to creating a regular table. The only difference is that we put a pound sign (#) in front of the name of the table name.
CREATE TABLE #ourTable ( ID int, fieldTwo varchar(50), fieldThree int )
Then, later in our code, we can use it like a regular table:
INSERT INTO #ourTable ([ID] , [fieldTwo], [fieldThree]) VALUES(@ID, @fieldTwo,@fieldThree)
As a dotNet programmer there is a small quirk about temporary tables you need to know about. If you return a temporary table from a stored procedure, the wizards will not be able to see it so that they can create your DataTable in your DataSet correctly. My suggested work-around for this is to create a real table and have your stored procedure use that table while you are running the DataTable wizard. Then, once you have the DataTable, change your stored procedure back to using the temporary table.
Other post in SQL For Programmers
- SQL For Developers - 9 Reasons to bother - August 13th, 2008
- MSSQL CREATE and DROP Database - SQL for Programmers - August 20th, 2008
- MSSQL CREATE TABLE - SQL For Programmers - August 22nd, 2008
- SQL For Programmers - ALTERing the TABLE - September 1st, 2008
- SQL For Programmers - Finding IN a List - September 8th, 2008
- SQL For Programmers - Stored Procedure Basics - September 16th, 2008
- Basic SQL Commands - SQL For Programmers - September 18th, 2008
- SQL IF/WHILE Blocks - SQL For Programmers - September 30th, 2008
- Temporary Tables - SQL For Programmers - October 7th, 2008
- SQL CURSOR - SQL For Programmers - October 13th, 2008
- SQL CURSOR Performance - SQL For Programmers - October 22nd, 2008
- Random in SQL - SQL For Programmers - November 4th, 2008
- SQL - Filtering WHERE condition on two rows - November 26th, 2008
- SQL - Transactions - April 15th, 2009
- SQL For Programmers – New Question - July 6th, 2009
- SQL SELECT CASE Instead of IIF - October 20th, 2009
- SQL For Programmers - Stored Procedures (Better than LINQ) - August 28th, 2012
- SQL for .NET Programmers–The Book - July 29th, 2013
- SQL For Programmers - Finding a String - December 4th, 2013
- SQL WHILE - SQL For Programmers - February 12th, 2014