dynamic table creation (table name as variable)

[TerraSamba]

I want to create and reference some tables dynamicly in a Stored Proc.

For instance

DECLARE @Tablename VARCHAR(50)
DECLARE @Count INT

WHILE @Count<5
BEGIN
SELECT *
INTO @Tablename + CAST(@COUNT AS VARCAHR(5))
FROM MyTable
WHERE MyCol1=100 + @Count

SET @Count=@Count+1
END

Is there a way to reference tables through their names with the use of a variable for their name?

I do not need work-arounds for this problem. I already solved that part, but I want to know if their is a way to refer to a table in SQL by means of a (string type) variable.

Thanx,

The gap between theory and practice is not as wide in theory as it is in practice.

 

[JamesLean]

You must use dynamic SQL to do this:

DECLARE @tbl varchar(20)
SET @tbl = 'table1'

EXEC('SELECT * FROM ' + @tbl)

--James

 

[Sabnac]

Create the stmt into a nvarchar variable and use the sp_executesql

Declare @stmt nvarchar(2500)

Set @stmt =
'SELECT * INTO' + @Tablename +
'CAST(@COUNT AS VARCAHR(5))
FROM MyTable
WHERE MyCol1=100 + ' + @Count

sp_executesql @stmt

 

[TerraSamba]

Super thanx !


The gap between theory and practice is not as wide in theory as it is in practice.