One of my all time favorite features of MSSQL 2005+ is being able to create table variables on the fly from SELECT statements. This isn’t a lesson in what table variables are, but here is an easy sample in case this is a new concept.
Running this query
SELECT * INTO #myTableVar FROM YourTable
Gives you a new table variable named myTableVar. Table variables are scoped to the active connection, so running this will work.
// make table var
SELECT * INTO #myTableVar FROM YourTable
// show me the data
SELECT * FROM #myTableVar
// you can drop it if you wish
DROP TABLE #myTableVar
However, let’s say you have an aspx page or a sproc that runs this query.
SELECT * INTO #myTableVar FROM YourTable
You can not access myTableVar in a separate connection to the database because as soon as the first query’s connection closes, myTableVar gets dropped. Here are a few other scenarios that also demonstrate the scoping of a table variable.
-- FAILS
EXEC ('SELECT * INTO #tmp FROM MyTable;');
-- #tmp does not exist
SELECT * FROM #tmp
Table variable #tmp lives inside of EXEC
Here we see that the table variable #tmp only lives for the life of the statement inside of EXEC. The second SELECT * calls is outside of the EXEC statement.
-- #tmp2 works inside of EXEC statement
EXEC ('SELECT * INTO #tmp2 FROM MyTable; SELECT * FROM #tmp2');
What happens in EXEC, stays in EXEC
Here #tmp2 works because it’s being used inside of the EXEC statement. This is worth knowing if you work with dynamic sql statements and exec.
-- works!
SELECT * INTO #tmp FROM MyTable;
-- #tmp exists
SELECT * FROM #tmp
typical sample of using mssql table variable
This is a typical example that you may use inside a sproc, trigger, script, etc. Both sql calls live in the same space, so #tmp exists.
Tags: exec, mssql, mssql2000, mssql2005, scope, SELECT INTO, table variable, temp table, tsql
Posted in database, development, random, tsql | 1 Comment »
Earlier I blogged about creating random numbers using tsql functions. Here are two techniques for selecting a random value from a pre-defined range of values in a tsql script. The first technique uses a table variable ( MSSQL 2000 + ), and the second uses a Common Table Expression or CTE ( MSSQL 2005+ ).
Select a random value using a table variable
-- var to hold random integer
declare @field_val int
-- create table var to hold value range [ 0, 512, 1024, 2048, 4096 ]
-- inserting the first value sets the structure for the table variable
SELECT 0 AS 'num'
INTO #temp
-- insert data into table var
INSERT INTO #temp VALUES ( 512 )
INSERT INTO #temp VALUES ( 1024 )
INSERT INTO #temp VALUES ( 2048 )
INSERT INTO #temp VALUES ( 4096 )
-- assign random value
SELECT TOP 1 @field_val = num FROM #temp ORDER BY NEWID()
-- show value
SELECT @field_val
-- drop the table variable
DROP TABLE #temp
Select a random value using a CTE
-- define our data table
WITH data( car )
AS
(
-- UNION together our range of values
SELECT 'audi' AS 'car'
UNION
SELECT 'bmw' AS 'car'
UNION
SELECT 'infinity' AS 'car'
UNION
SELECT 'lexus' AS 'car'
UNION
SELECT 'porsche' AS 'car'
)
-- select a random value
SELECT TOP 1 car FROM data
ORDER BY NEWID()
Both of these techniques can be used with numbers or text. Just be sure to mind your quotes, and variable datatypes. Being able to pick a random value in data generation scripts has proven very useful. I hope this helps somebody else out as well.
Tags: #table, cte, howto, mssql, mssql2000, mssql2005, random, table variable, tsql
Posted in database, development, microsoft, random, tips and tricks, tsql | 1 Comment »
