How Can I Use a Variable as a Table Name in SQL Queries?

Declare the table name as a variable

Introduction:

Trying to use a variable as a table name in a static query results in the error "Table variable must be declared". This article explores the correct way to populate table names dynamically.

Solution:

For static queries, table names and column names must be static. However, dynamic queries allow for dynamic generation of complete SQL statements, which can be executed using sp_executesql.

Consider the following scenario:

declare @tablename varchar(50)
set @tablename = 'test'
select * from @tablename

This static query will fail because the table name is dynamically assigned.

Dynamic query example:

The following script demonstrates how to use dynamic queries to compare data in two databases:

declare @schema sysname;
declare @table sysname;
declare @query nvarchar(max);

set @schema = 'dbo'
set @table = 'ACTY'

set @query = '
SELECT * FROM [DB_ONE].' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + '
EXCEPT
SELECT * FROM [DB_TWO].' + QUOTENAME(@schema) + '.' + QUOTENAME(@table);

EXEC sp_executesql @query

In this example, the table names and schema are dynamic, allowing easy comparison of data in different databases.

Note:

Dynamic queries require careful consideration and maintenance. It is recommended to consult resources such as "The Curse and Blessing of Dynamic SQL" to learn about best practices.

The above is the detailed content of How Can I Use a Variable as a Table Name in SQL Queries?. For more information, please follow other related articles on the PHP Chinese website!