如何从SQL Server存储过程中的变量将数据插入另一个表

I would like to insert the data from a variable (which is a table name) into another table inside a stored procedure. But when I try altering the stored proc I get an error. What am I doing wrong?

我想将变量(表名)中的数据插入存储过程中的另一个表中。但是当我尝试改变存储过程时,我得到一个错误。我究竟做错了什么?

SQL:

INSERT INTO DBNAME..Table (Col1, Col2, Col3)
    SELECT Col1, Col2, Col3
    FROM @Tablevariable;

Error:

Must declare the table variable "@Tablevariable".

必须声明表变量“@Tablevariable”。

@Tablevariable is already declared in my stored procedure.

@Tablevariable已在我的存储过程中声明。

2 个解决方案

#1

You cannot have the table-name be a variable. You could do something like this in the stored procedure:

你不能让table-name成为变量。您可以在存储过程中执行以下操作:

DECLARE @Tablevariable nvarchar(50) = 'MyTableName'
DECLARE @SQL nvarchar(MAX)

SET @SQL = 'INSERT INTO DBNAME..Table (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM ' + @Tablevariable

EXECUTE (@SQL)

#2

I don't think there is a way of doing FROM @TableVariable. I think instead you'd need to do either:

我认为没有办法做FROMTableVariable。我认为你需要做任何一件事:

Stick a case statement in there so if @TableVariable = 'Customers' select from customers table. The problem with is though every time you create a new table you must update your stored proc.

如果@TableVariable ='Customers'从customers表中选择,那么将case语句粘贴在那里。问题是每次创建新表时都必须更新存储过程。
Build out the text as a string within SQL: DECLARE sqlCommand VARCHAR(MAX) = 'Select blah blah blah FROM ' + @TableVariable (or something like that). Then call exec(sqlCommand). The problem with this is though, you wont' get query optimization on the sqlCommand.

在SQL中将文本构建为字符串:DECLARE sqlCommand VARCHAR(MAX)='选择blah blah blah FROM'+ @TableVariable(或类似的东西)。然后调用exec(sqlCommand)。但问题是,您不会在sqlCommand上获得查询优化。

#1