如何在sql中定义变量而不是表名? [英] How to define a variable instead of table name in sql?

问题描述

我有这个 SQL 查询:

I have this SQL query:

delete from Main.dbo.ACTIVITY;
insert into Main.dbo.ACTIVITY
select * from MainTemp.dbo.ACTIVITY;

并且我想在为 ACTIVITY 执行后为 10 个表执行那个 SQL.

and I want to execute that SQL for 10 tables after executing for ACTIVITY.

有没有办法做到这一点?定义一个变量什么的?

Is there any way to do that? Defining a variable or something?

我在 SQL Server Management Studio 10 (= SQL Server 2008) 中运行查询

I'm running the query in SQL Server Management Studio 10 (= SQL Server 2008)

推荐答案

使用动态 SQL

declare @tablename sysname = N'ACTIVITY';
declare @sql  nvarchar(max);

set @sql = N'delete from Main.dbo.' + quotename(@tablename);
exec sp_executesql @sql;
set @sql = N'insert into Main.dbo.' + quotename(@tablename);
exec sp_executesql @sql;
set @sql = N'select * from Main.dbo.' + quotename(@tablename);
exec sp_executesql @sql;

非常使用QUOTENAME 在处理动态 SQL 时，因为 SQL 注入风险.动态 SQL 有利有弊，有关详细讨论，请参阅动态 SQL 的诅咒和祝福.

It is very important to use the QUOTENAME when handling dynamic SQL because of the SQL injection risk. Dynamic SQL has pros and cons, for an in dept discussion see The Curse and Blessings of Dynamic SQL.

SSMS 和 SQLCMD 具有使用客户端变量替换的能力:

SSMS and SQLCMD have capabilities to use client side variable substitution:

:setvar tablename Main.dbo.ACTIVITY
delete from $(tablename);
insert into $(tablename);
select * from $(tablename);

SQLCMD 模式变量在批量使用时大放异彩，因为这些变量可以通过 -v 参数.例如:

Where SQLCMD mode variables shine is when used in batches because the variables can be passed in via the -v argument. For example:

c:\>for /f %i in (tablenames.txt) do sqlcmd -S <servername> -E -d <dbname> -v tablename=%i -Q "truncate table $(tablename)"

请注意，在 SSMS 中，必须显式启用 SQLCMD 执行模式.

Note that in SSMS the SQLCMD execution mode must be enabled explicitly.

这篇关于如何在sql中定义变量而不是表名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！