使用动态变量名创建 SQL 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2905878/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating SQL table using dynamic variable name
提问by stevenjmyu
I want to create backup SQL tables using variable names.
我想使用变量名创建备份 SQL 表。
something along the lines of
类似的东西
DECLARE @SQLTable Varchar(20)
SET @SQLTable = 'SomeTableName' + ' ' + '20100526'
SELECT * INTO quotename(@SQLTable)
FROM SomeTableName
but i'm getting
但我得到
Incorrect syntax near '@SQLTable'.
'@SQLTable' 附近的语法不正确。
It's just part of a small script for maintence so i don't have to worry about injections.
它只是用于维护的小脚本的一部分，因此我不必担心注入。
回答by John Hartsock
DECLARE @MyTableName nvarchar(20);
DECLARE @DynamicSQL nvarchar(1000);
SET @MyTableName = "FooTable";
SET @DynamicSQL = N'SELECT * INTO ' + @MyTableName + ' FROM BarTable';
EXEC(@DynamicSQL);
回答by Donnie
Unfortunately, you can't use bind variables for table names, column names, etc. IN this case you must generate dynamic SQL and use exec.
不幸的是，您不能将绑定变量用于表名、列名等。在这种情况下，您必须生成动态 SQL 并使用exec.
回答by Don
DECLARE @Script NVARCHAR(MAX);
SET @Script = N'SELECT * INTO SomeTableName_' + N'20100526' + N' FROM SomeTableName';
EXEC sp_executesql @Script
I've left the date separate as I assume you want to calculate it for every run.
我已将日期分开，因为我假设您想为每次运行计算它。
回答by Tyson Nero
You should look into using synonyms:
您应该考虑使用同义词：
-- Create a synonym for the Product table in AdventureWorks2008R2. CREATE SYNONYM MyProduct FOR AdventureWorks2008R2.Production.Product; GO
-- 在 AdventureWorks2008R2 中为 Product 表创建同义词。为 AdventureWorks2008R2.Production.Product 创建同义词 MyProduct；走
-- Query the Product table by using the synonym. USE tempdb; GO SELECT ProductID, Name FROM MyProduct WHERE ProductID < 5; GO
-- 使用同义词查询Product表。使用临时数据库；GO SELECT ProductID, Name FROM MyProduct WHERE ProductID < 5; 走
回答by Chandu
DECLARE @MyTableName nvarchar(20);
DECLARE @DynamicSQL nvarchar(1000);
SET @MyTableName = "FooTable";
SET @DynamicSQL = N'SELECT * INTO ' + @MyTableName + ' FROM BarTable';
exec @DynamicSQL;
this query is correct but just use single quote at the ("FooTable")='FooTable'
此查询是正确的，但只需在 ("FooTable")='FooTable' 处使用单引号
