使用动态变量名创建 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' 处使用单引号