使用动态变量名创建 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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-01 06:21:27  来源:igfitidea点击:

Creating SQL table using dynamic variable name

sqlsql-serverdatabasetsqldynamic-sql

提问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; 走

http://msdn.microsoft.com/en-us/library/ms177544.aspx

http://msdn.microsoft.com/en-us/library/ms177544.aspx

回答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' 处使用单引号