如何将存储过程的结果插入SQL Server中的临时表中 [英] How to insert the results of a stored procedure into a temporary table in SQL server
问题描述
我想从表格中获取用户指定列的数据。
我尝试过的事情:
- 虚假表
CREATE TABLE #tbEmployeeMaster(EmployeeId INT IDENTITY(1,1),EmployeeName VARCHAR(100),FatherName VARCHAR(100),MotherName VARCHAR(100),年龄INT)
INSERT INTO #tbEmployeeMaster(EmployeeName,FatherName,MotherName,Age)VALUES('Aman','Rakesh','Anita' ,22)
INSERT INTO #tbEmployeeMaster(EmployeeName,FatherName,MotherName,Age)VALUES('Sunil','Sandeep','Kavita',22)
DECLARE @SelectedColumns VARCHAR(500)='EmployeeName,Age';
- 注意:用户可以按任何顺序选择上面的列,例如年龄,员工姓名
- 我尝试动态sql如下。
DECLARE @SQL VARCHAR(2000)
SET @SQL ='SELECT'+ @SelectedColumns +'FROM #tbEmployeeMaster'
EXEC(@SQL) - 工作原理
- 我想使用下面的语法将数据插入临时表,以便我可以进一步使用它。但它不起作用。我知道这里不能使用table varible,因为我们不知道列顺序。
SELECT * INTO #tbEmp EXEC(@SQL)
由于程序的范围,无论如何都不能使用表变量。
你必须添加'select into'进入查询:
DECLARE @ SQL VARCHAR ( 2000 )
< span class =code-keyword> SET @ SQL = ' SELECT' + @ SelectedColumns + ' INTO #tbEmp FROM #tbEmployeeMaster'
EXEC ( @ SQL )
使用select-into self定义表结构,因为我相信你已经知道了。
我担心你的解决方案不会像你想象的那样工作。在执行存储过程之前,您需要提前创建#tbEmp / schema。或者使用OPENROWSET功能。或使用动态SQL编写整个解决方案。
创建 TABLE #tbEmp(EmployeeName VARCHAR ( 100 ),年龄 INT )
...
INSERT INTO #tbEmp EXEC ( @ SQL )
sql - 将存储过程的结果插入临时表 - Stack Overflow [ ^ ]
https:// blog .sqlauthority.com / 2013/5月27日/ SQL-服务器如何对插入数据从 - 存储过程到-table -2-不同的方法/
I want to get the data from tables for the user specified columns.
What I have tried:
--dummy table
CREATE TABLE #tbEmployeeMaster (EmployeeId INT IDENTITY(1,1), EmployeeName VARCHAR(100), FatherName VARCHAR(100), MotherName VARCHAR(100), Age INT)
INSERT INTO #tbEmployeeMaster (EmployeeName ,FatherName , MotherName, Age) VALUES ('Aman', 'Rakesh', 'Anita', 22)
INSERT INTO #tbEmployeeMaster (EmployeeName ,FatherName , MotherName, Age) VALUES ('Sunil', 'Sandeep', 'Kavita', 22)
DECLARE @SelectedColumns VARCHAR(500)='EmployeeName,Age';
--Note: User can select above columns in any order e.g. Age,EmployeeName
--I tried dynamic sql as follows.
DECLARE @SQL VARCHAR(2000)
SET @SQL = 'SELECT ' + @SelectedColumns + ' FROM #tbEmployeeMaster'
EXEC(@SQL) --it works
--I want to insert the data into temporary table using the syntax below so that i can use that further.But IT did not work. I know table varible can not be used here because we don't know the column order.
SELECT * INTO #tbEmp EXEC(@SQL)
You can't use a table variable anyway because of the scope of the procedures.
You have to add the 'select into' into the query:
DECLARE @SQL VARCHAR(2000) SET @SQL = 'SELECT ' + @SelectedColumns + ' INTO #tbEmp FROM #tbEmployeeMaster' EXEC(@SQL)
using select-into self defines the table structure, as i'm sure you already know.
I'm afraid your solution will not work as you envision. You will need to create the #tbEmp /schema ahead of time before executing the Stored Procedure. Or use the OPENROWSET function. or write the whole solution using dynamics SQL .
CREATE TABLE #tbEmp (EmployeeName VARCHAR(100), Age INT) ... INSERT INTO #tbEmp EXEC(@SQL)
sql - Insert results of a stored procedure into a temporary table - Stack Overflow[^]
https://blog.sqlauthority.com/2013/05/27/sql-server-how-to-insert-data-from-stored-procedure-to-table-2-different-methods/
这篇关于如何将存储过程的结果插入SQL Server中的临时表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!