Table name as a variable

Question

Hello everyone ,

I want to pass a table name as a variable, here is what I am trying to do, please advise, thank you.
I have test_table_old which have create_date column and I am trying to insert data into temp_table_new only after the max create date from the Temp_table_new, I can do this fine for one table, but I have a few of them and I wanted to pass names of the table as a parameter, please advise, thank you.

DECLARE @createdatetemp DATETIME, @Tablename VARCHAR(50)

SET @Tablename= Any of the tem_test_old
SET @createdatetemp= (SELECT MAX(create_date) FROM @Tablename)

INSERT INTO Temp_table_new
SELECT *
FROM tem_test_old
WHERE create_date >= @createdatetemp

Answer 1

Table names cannot be dynamic. In order to do what you are trying to do would require "dynamic SQL" which basically means generating a string with the select statement you are trying to run, and then executing the string using EXEC.

Answer 2

You can create a variable @TablenameList to hold the names of the tables you want to process:

DECLARE @TablenameList varchar(1000);
SET @TablenameList = 'Table1, Table2, ...';

And then you can using the cursor or WHILE loop to go through the result of splitting the list using the system function STRING_SPLIT() if your SQL server is 2016 or later:

DECLARE @sql varchar(max) = '';
DECLARE Table_Cursor CURSOR FOR
SELECT value AS Tablename FROM STRING_SPLIT(@TablenameList, ',');
OPEN Table_Cursor;
FETCH NEXT FROM Table_Cursor INTO @Tablename;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = '
        INSERT INTO Temp_table_new  
        SELECT *
        FROM tem_test_old
        WHERE create_date >= SELECT MAX(create_date) FROM ' + @Tablename;
    EXEC(@sql);

    FETCH NEXT FROM Table_Cursor INTO @Tablename;
END
CLOSE Table_Cursor;
DEALLOCATE Table_Cursor;
GO

Answer 3

I can do this fine for one table, but I have a few of them and I wanted to pass names of the table as a parameter, please advise, thank you.

How did that happen? If this is a one-off you can generate the statements for the insert.

If this is something you are considering to put into application code, you should reconsider your design so that you don't have all these tables. A table in a relational database is supposed to model a unique entity with its unique set of attributes. For this reason, there is little point in having variables for table names.

Answer 4

Hi @DataNerd ,

Welcome to Microsoft Q&A!

You could refer below query instead when you would like to insert data into temp_table_new only after the max create date from the Temp_table_new.

INSERT INTO Temp_table_new  
SELECT * FROM tem_test_old  
WHERE create_date >= (SELECT MAX(create_date) FROM tem_test_old)  

If you would like to pass a table name as a variable and you have some basic knowledge of dynamic sql statement, you could refer below:

DECLARE @SQL NVARCHAR(max)  
DECLARE @Tablename VARCHAR(50)  
  
SET @Tablename='tem_test_old'  
  
SET @SQL=N'INSERT INTO Temp_table_new  
SELECT * FROM ' +@Tablename+' WHERE create_date >= (SELECT MAX(create_date) FROM '+@Tablename+')'  
  
exec (@SQL)  

OR

DECLARE @SQL NVARCHAR(max)  
DECLARE @Tablename VARCHAR(50)  
      
SET @Tablename='tem_test_old'  
      
SET @SQL=N'INSERT INTO Temp_table_new  
SELECT * FROM tem_test_old WHERE create_date >= (SELECT MAX(create_date) FROM '+@Tablename+')'  
      
exec (@SQL)  

If you would like to do this with a few of tables at the same time, you could refer the cursor mentioned by Guoxiong.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.