将存储过程结果插入 SQL 临时表

如何编写查询以将存储过程结果插入 SQL Server 的临时表，并附带示例。对于这个面试问题，我们将使用下面显示的数据。数据存在于我们教程数据库的 Employee 表中。

在此 SQL Server 示例中，我们将使用下面显示的存储过程，它将选择 Employee 表中的所有记录。

-- Insert Stored Procedure result into Temporary Table Example
 
IF OBJECT_ID ( 'SelectStoredProcedureFirstExample', 'P' ) IS NOT NULL   
    DROP PROCEDURE SelectStoredProcedureFirstExample;  
GO
 
CREATE PROCEDURE SelectStoredProcedureFirstExample
AS
BEGIN
      SET NOCOUNT ON;
      SELECT [FirstName] + ' ' + [LastName] AS [Full Name]
            ,[Education]
            ,[Occupation]
            ,[YearlyIncome]
            ,[Sales]
            ,[HireDate]
      FROM [Employee]
END
GO
Messages
--------
Command(s) completed successfully.

我建议您参考 存储过程 和 临时表 文章以获得基本概念。同时参考 Select Statement。

将存储过程结果插入 SQL 临时表示例 1

在此 Server 示例中，我们将创建一个临时表。接下来，我们将使用 INSERT INTO SELECT Statement 将存储过程结果插入临时表。

--  Insert Stored Procedure result into Temporary Table Example
-- Creating Temporary Table
CREATE TABLE #Employee(
	[FullName] [nvarchar](650) NULL,
	[Education] [nvarchar](255) NULL,
	[Occupation] [nvarchar](255) NULL,
	[YearlyIncome] [float] NULL,
	[Sales] [float] NULL,
	[HireDate] [datetime] NULL
)
GO

--Inserting Records from Stored procedure to Temporary Table
INSERT INTO #Employee
EXEC SelectStoredProcedureFirstExample
GO

-- Selecting Records from Temporary Table
SELECT * FROM #Employee
Insert Stored Procedure result into Temporary Table in SQL Server 3

将存储过程结果插入临时表示例 2

在此常见问题解答中，我们使用 SELECT INTO Statement 和 OPENROWSET 将存储过程结果插入临时表。

sp_configure 'show advanced options', 1;  
RECONFIGURE;
GO 
sp_configure 'Ad Hoc Distributed Queries', 1;  
RECONFIGURE;  
GO 
 
--Inserting Records from Stored procedure to Temporary Table
SELECT * INTO #Employee 
FROM OPENROWSET('SQLNCLI', 
		'Server=(local);Trusted_Connection=yes;',
		'EXEC dbo.SelectStoredProcedureFirstExample')

-- Selecting Records from Temporary Table
SELECT * FROM #Employee
Insert Stored Procedure result into Temporary Table 4