First of all I'm using ms SQL server 2012. I' trying to use a table based on a string value passed in as a string into a procedure. I found out that you can't use strings are table names when writing a query so I'm trying to find a way around that. The only lead I'm kind of onto is using dynamic SQL which I also am not sure how to make work. Here is what I have:
首先，我使用的是ms SQL server 2012。我尝试使用一个基于字符串值的表作为一个字符串传递到一个过程中。我发现，在编写查询时，不能使用字符串作为表名，因此我试图找到一种方法来解决这个问题。我所使用的唯一线索是使用动态SQL，我也不知道该如何工作。以下是我所拥有的:
DECLARE @q AS NVARCHAR(MAX)
SET @q = 'SELECT * FROM ' + @tableName
DECLARE #tableCopy AS EXECUTE(@q)
How can I get the executed @q into #tableCopy? Or is there a better way to access my table when all I know is the tables name as a string?
如何将@q执行到#tableCopy?或者，当我只知道表名为字符串时，是否有更好的方法来访问我的表?
1 个解决方案
#1
0
You can create the temporary table and then insert into that table inside the dynamic sql. There's an example here: http://smehrozalam.wordpress.com/2009/10/14/t-sql-using-result-of-a-dynamic-sql-query-in-a-variable-or-table/
您可以创建临时表，然后在动态sql中插入该表。这里有一个例子:http://smehrozalam.wordpress.com/2009/10/14/t-sql- result- a-dynamic-sql-query- -or-table/。
Unfortunately, you would need to know the schema. The following does not work:
不幸的是，您需要知道模式。以下不可行:
declare @query varchar(max) =
'select * into #t from table'
EXEC(@query)
select * FROM #t
#1
0
You can create the temporary table and then insert into that table inside the dynamic sql. There's an example here: http://smehrozalam.wordpress.com/2009/10/14/t-sql-using-result-of-a-dynamic-sql-query-in-a-variable-or-table/
您可以创建临时表，然后在动态sql中插入该表。这里有一个例子:http://smehrozalam.wordpress.com/2009/10/14/t-sql- result- a-dynamic-sql-query- -or-table/。
Unfortunately, you would need to know the schema. The following does not work:
不幸的是，您需要知道模式。以下不可行:
declare @query varchar(max) =
'select * into #t from table'
EXEC(@query)
select * FROM #t