How to create and use table variables in T-SQL
Part two of a three-part series of blogs

Table variables allow you to hold rows of data in temporary storage, without any processing overhead. Read this blog to add this technique to your SQL armoury!

  1. Table Variables in SQL
  2. Worked Example of a Table Variable (this blog)
  3. Pros and cons of table variables

This blog is part of our full online SQL tutorial.  For a more personal experience, have a look at our SQL training courses for businesses.

Posted by Andy Brown on 08 February 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Worked Example of a Table Variable

Regular readers will know that many of our SQL examples relate to a simple Movies database, containing a small sample of famous films, with their directors, studios, etc.

Imagine now that you've been asked to draw up a list of experienced people in the film industry, to whose works you can turn for advice (no matter that the people themselves might no longer be alive). 

Avoiding a table variable using a UNION query

As is nearly always the case, you don't have to use a table variable at all for our example; you could just use a UNION keyword:

-- looking for experience? Create a query to show a list of directors

-- born before 1914 and actors who have appeared in at least 9 films

-- get the directors

SELECT

DirectorName AS PersonName,

'Director' AS PersonRole,

DirectorDob AS BirthDate

FROM

tblDirector

WHERE

DirectorDob <>'19140101'

 

-- and add on the actors

UNION ALL

 

SELECT

a.ActorName,

'Actor',

ActorDob

FROM

tblActor AS a

WHERE (

SELECT COUNT(*) FROM tblCast AS c

WHERE a.ActorId=c.CastActorId

) >= 9

For the Wise Owl database, running this would give 3 directors and 2 actors:

Old actors and directors

Clearly the Wise Owl database doesn't have enough Meryl Streep films in!

 

However, what if you now want to perform further processing based on this list of "old hands"?  You could create a common table expression, or CTE:

WITH OldHands AS (

 

-- get the directors

SELECT

DirectorName AS PersonName,

'Director' AS PersonRole,

DirectorDob AS BirthDate

FROM

tblDirector

WHERE

DirectorDob <>'19140101'

 

-- and add on the actors

UNION ALL

SELECT

a.ActorName,

'Actor',

ActorDob

FROM

tblActor AS a

WHERE (

SELECT COUNT(*) FROM tblCast AS c

WHERE a.ActorId=c.CastActorId

) >= 9

)

 

-- use this CTE to show details

SELECT

PersonName,

PersonRole,

CONVERT(char(10),BirthDate,103) AS DOB

FROM

OldHands

ORDER BY

PersonName

However, a CTE only allows you to manipulate the table immediately after it has been created.  We want a longer-lasting access method.  One solution is to create a table variable.

Creating and using a table variable

One way to solve the above problem which produces SQL which is easy to write, understand and maintain is to store the rows in a table variable.  The first thing to do is to create this (you can read more on creating tables in SQL here):

-- first create variable to hold table of answers

DECLARE @OldHands TABLE (

PersonName varchar(50),

PersonRole varchar(50),

BirthDate datetime

)

You can now put the old directors into this table (you can see more about inserting bulk rows using SQL here):

-- get the directors

INSERT INTO @OldHands

SELECT

DirectorName AS PersonName,

'Director' AS PersonRole,

DirectorDob AS BirthDate

FROM

tblDirector

WHERE

DirectorDob <>'19140101'

Now it's time to add in the experienced actors:

-- and add on the actors

INSERT INTO @OldHands

SELECT

a.ActorName,

'Actor',

ActorDob

FROM

tblActor AS a

WHERE (

SELECT COUNT(*) FROM tblCast AS c

WHERE a.ActorId=c.CastActorId

) >= 9

And finally, we can manipulate the results:

-- use this to show details

SELECT

PersonName,

PersonRole,

CONVERT(char(10),BirthDate,103) AS DOB

FROM

@OldHands

ORDER BY

PersonName

The key point is that the @OldHands table will retain the records for the lifetime of this SQL batch (ie until a GO statement is reached).

Now we've seen what table variables are (and how to use them), the final part of this blog will consider their pros and cons.

  1. Table Variables in SQL
  2. Worked Example of a Table Variable (this blog)
  3. Pros and cons of table variables
This blog has 0 threads Add post