This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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! Table Variables in SQL Worked Example of a Table Variable (this blog) 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.

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!

Table Variables in SQL
Worked Example of a Table Variable (this blog)
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

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:

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.

Parts of this blog
Table Variables in SQL Worked Example of a Table Variable (this blog) Pros and cons of table variables

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Worked Example of a Table Variable

Avoiding a table variable using a UNION query

Creating and using a table variable

Head office

London

Manchester