Temporary Tables – SQL For Programmers

other-076 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.

Like this Article? Subscribe to get every article sent to your email.

Related Post

  • Random in SQL – SQL For ProgrammersRandom in SQL – SQL For Programmers Last week I had an interesting assignment: Given a specific record, randomly retrieve three related records from the database. Naturally, as a programmer, I started looking for the SQL […]
  • SQL CURSOR – SQL For ProgrammersSQL CURSOR – SQL For Programmers One of the things it took me a while to figure out was how to get a stored procedure to loop through data in a table and pull out specific data I needed. There are actually two parts […]
  • SQL For Programmers – Finding IN a ListSQL For Programmers – Finding IN a List In the last SQL post, we looked at looking for content that was LIKE other content.  While this has its uses, it is limited in its ability to find more than one pattern.  So what if we […]
  • SQL IF/WHILE Blocks – SQL For ProgrammersSQL IF/WHILE Blocks – SQL For Programmers OK, all you programmer types.  Here's where SQL starts getting interesting.  You have the basic concepts of declaring variables, setting up stored procedures, and doing a basic insert, […]
  • SQL CURSOR Performance – SQL For ProgrammersSQL CURSOR Performance – SQL For Programmers Last week I showed how to use the SQL CURSOR to loop through records in a database.  In that article I mentioned that you want to avoid using the CURSOR if you can because it has […]

About Dave Bush

Dave Bush is a .NET programmer and Certified ScrumMaster who is passionate about managing risk as it relates to developing software. When he is not writing or speaking about topics related to Application Lifecycle Risk Management (ALRM), he is an example to his peers as he develops web sites in the ASP.NET environment using industry best practices.

One Pingback/Trackback

Awards & Certs


Links