Creating SQL Table Using Dynamic Variable Name

Creating SQL table using dynamic variable name

DECLARE @MyTableName sysname;
DECLARE @DynamicSQL nvarchar(max);

SET @MyTableName = 'FooTable';

SET @DynamicSQL = N'SELECT * INTO ' + QUOTENAME(@MyTableName) + ' FROM BarTable';

EXEC sp_executesql @DynamicSQL;

Create a table using a variable as the table name

You can use dynamic TSQL:

DECLARE @INSFIRSTNAME VARCHAR (100) = 'Ted'
DECLARE @INSSURNAME VARCHAR (100) = 'Smith'
DECLARE @INSSTAFFNO VARCHAR (10) = 'AB123'
DECLARE @INSYEAR VARCHAR (10) = '2018'
DECLARE @TABLENAME VARCHAR (400) = 'Timesheet_' + @INSFIRSTNAME + @INSSURNAME + @INSSTAFFNO + '_' + @INSYEAR

--declare a variable that will hold your query
declare @sql_create nvarchar(max)

--create the query concatenating DDL instructions and variables
set @sql_create=''
set @sql_create= @sql_create + 'CREATE TABLE ' + @TABLENAME 
set @sql_create= @sql_create + ' (INSFIRSTNAME VARCHAR (100),'
set @sql_create= @sql_create + '  INSSURNAME VARCHAR (100),'
set @sql_create= @sql_create + '  INSSTAFFNO VARCHAR (10),'
set @sql_create= @sql_create + '  INSYEAR VARCHAR (10));'

set @sql_create= @sql_create + ' INSERT INTO ' + @TABLENAME
set @sql_create= @sql_create + '               (INSFIRSTNAME, INSSURNAME, INSSTAFFNO, INSYEAR)'
set @sql_create= @sql_create + ' VALUES        ('''+@INSFIRSTNAME+''','''+ @INSSURNAME + ''','''+ @INSSTAFFNO+ ''',''' + @INSYEAR + ''')'

--execute the query contained inside the variable
exec sp_executesql  @sql_create

Now you can select from your table:

Dynamic table name and variable name in query in SQL Server

Use sp_executesql and parameters:

DECLARE @table_name VARCHAR(50) = 'table_name';
DECLARE @valid_to datetime = getdate();

DECLARE @sql NVARCHAR(max) = N'
UPDATE '+ @table_name + N'
    SET valid_flag = 0, 
        valid_to = @valid_to
    WHERE valid_flag = 1
';

EXEC sp_executesql @sql, N'@valid_to datetime', @valid_to=@valid_to;

EDIT:

As recommended by Larnu a comment:

DECLARE @table_name sysname = 'table_name';
DECLARE @valid_to datetime = getdate();

DECLARE @sql NVARCHAR(max) = N'
UPDATE '+ QUOTENAME(@table_name) + N'
    SET valid_flag = 0, 
        valid_to = @valid_to
    WHERE valid_flag = 1
';

EXEC sp_executesql @sql, N'@valid_to datetime', @valid_to=@valid_to;

SQL: Dynamic Variable Names

Well, it is not pretty, but you can do:

if @loopcntr = 1
    set var01 = 'somevalue'
else if @loopcntr = 2
    set var02 = 'whatever'
else if . . .

This should be sufficiently unpleasant that you might think of alternatives. Oh, here's a good one. Define a table variable and just add rows in for each value:

declare @vars table (
    id int identity(1, 1),
    loopcntr int,
    value varchar(255)
);

. . .
-- inside the loop
    insert into @vars(loopcntr, value)
        select @loopcntr, 'whatever';

When you want to get a variable, you can do:

declare @var varchar(255);
select @var = value from @vars where loopcntr = <the one I want>;

How to Create a table on variable name in SQL Server?

Your code seems to work:

DECLARE @i INT = 0, @deptcount INT = 4;

while @i < @deptcount+1
Begin 
  --creating dynamic tables 
  declare @tablenames nvarchar(50)
  set @tablenames = '##dept'+CAST(@i as nvarchar)
  execute('create table '+@tablenames+' (deptno int, formno int, stdpr int, agg int)')
  set @i = @i +1
End

SELECT *
FROM ##dept1
UNION ALL
SELECT *
FROM ##dept2
UNION ALL
SELECT *
FROM ##dept3;

LiveDemo

But reconsider your approach:

CREATE TABLE @tbl

The desire here is to create a table of which the name is determined
at run-time.

If we just look at the arguments against using dynamic SQL in stored
procedures, few of them are really applicable here. If a stored
procedure has a static CREATE TABLE in it, the user who runs the
procedure must have permissions to create tables, so dynamic SQL will
not change anything. Plan caching obviously has nothing to do with it.
Etc.

Nevertheless: Why? Why would you want to do this? If you are creating
tables on the fly in your application, you have missed some
fundamentals about database design. In a relational database, the set
of tables and columns are supposed to be constant. They may change
with the installation of new versions, but not during run-time.

Sometimes when people are doing this, it appears that they want to
construct unique names for temporary tables. This is completely
unnecessary, as this is a built-in feature in SQL Server. If you say:

CREATE TABLE #nisse (a int NOT NULL)

then the actual name behind the scenes will be something much longer,
and no other connections will be able to see this instance of #nisse.

Dynamic Variable Name for backup table

You have to convert your date variable to a string so you can concatenate it:

SET @CreateDynamicSQL='CREATE TABLE [dbo].[paul_AccountContact_Backup_' 
+ convert(varchar(20), @SYSDATETIME, <Format>) + '](

Documentation for the format parameter:

https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

How to use a Database Variable Name in dynamic SQL with visual studio for a SQL Server Project?

You can use the project deployment variable directly in the dynamic SQL, just be sure to correctly quote it:

SELECT @sql = N'SELECT QUOTENAME(b.[COLUMN_NAME]) AS [Column] 
                  FROM ' + QUOTENAME('$(DatabaseName)') + N'.[INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] a 
                  JOIN ' + QUOTENAME('$(DatabaseName)') + N'.[INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE] b 
                    ON a.[CONSTRAINT_NAME] = b.[CONSTRAINT_NAME] 
                 WHERE a.[CONSTRAINT_SCHEMA] = ' + QUOTENAME(@schema, '''') + N'
                   AND a.[TABLE_NAME] = ' + QUOTENAME(@table, '''') + N'
                   AND a.[CONSTRAINT_TYPE] = ''PRIMARY KEY'' '

Select into tables dynamically with variables

Is this a one time thing or do you have to do this on a regular basis?

If it's the first, than I would just do it and get it over with.

If it's the latter, then I suspect something is very wrong with the way that system is designed - but assuming that can't be changed, you can create a stored procedure that will do this using dynamic SQL.

Something like this can get you started:

CREATE PROCEDURE dbo.CreateTableBasedOnDate
(
    @MyDate DATE, 
    -- sysname is a system data type for identifiers: a non-nullable nvarchar(128).
    @TableName sysname
)
AS
-- 200 is long enough. Yes, I did the math. 
DECLARE @Sql nvarchar(200) = 
    -- Note: I'm not convinced that quotename is enough to protect you from sql injection.
    -- you should be very careful with what user is allowed to execute this procedure.
    N'SELECT * into '+ QUOTENAME(@TableName) +N'
    FROM Original_Table 
    WHERE Query_Date = @MyDate;';

-- When dealing with dynamic SQL, Print is your best friend.
-- Remark this row and unremark the next only once you've verified you get the correct SQL
PRINT @SQL;

--EXEC sp_ExecuteSql @Sql, N'@MyDate Date', @MyDate

GO

Usage:

EXEC CreateTableBasedOnDate '2018-01-01', 'zohar';

Dynamic SQL Select variable name

QUOTENAME function has a second optional parameter to wrap the value with a different character, in your case you need a single quote to use the @TableName as varchar value in the first use and as a table name in the second one

DECLARE @TableName NVARCHAR(250);
SET @TableName = N'TBL_Name'
DECLARE @SQL NVARCHAR(MAX) = N'SELECT ' + QUOTENAME(@TableName,'''') + N' as TableName, HASHBYTES(''SHA1'', (SELECT * FROM ' + QUOTENAME(@TableName) + N' FOR XML RAW)) As TableHash'
EXEC(@SQL);

It will create the following code

SELECT 'TBL_Name' as TableName, HASHBYTES('SHA1', (SELECT * FROM [TBL_Name] FOR XML RAW)) As TableHash

---

Related Topics