01. Select into from the stored procedure result set to the temporary table

Original article: 01. Select into from the stored procedure result set to a temporary table

In the development process, the result set is often stored in a temporary table. There are two common methods.

I. Select
1. Using select into will automatically generate a temporary table without creating it in advance.

select * into #temp from sysobjectsselect * from #temp

 

2. If a temporary table with the same name already exists in the current session

select * into #temp from sysobjects

 

If you run it again, an error message is displayed, indicating that the database name '% 1 already exists! 'Object.
MSG 2714, level 16, state 6, line 2
There is already an object named '# temp' in the database.

Before using select into, you can first make a judgment:

if OBJECT_ID(‘tempdb..#temp‘) is not nulldrop table #tempselect * into #temp from sysobjects select * from #temp

 

3. Use select into to generate an empty table
If you want to generate an empty table structure that does not contain any data, you can give a constant inequality as follows:

select * into #temp from sysobjects where 1=2select * from #temp

 

 

Ii. insert
1. To use insert into, you must manually create a temporary table.

1.1 Save the result set returned from the SELECT statement.

create table test_getdate(c1 datetime)
insert into test_getdate select GETDATE()
select * from test_getdate

 

1.2 Save the result set returned from the Stored Procedure

create table #helpuser(UserName nvarchar(128),RoleName nvarchar(128),LoginName nvarchar(128),DefDBName nvarchar(128),DefSchemaName nvarchar(128),UserID smallint,SID smallint)insert into #helpuser exec sp_helpuserselect * from #helpuser

 

1.3 Save the result set returned from the dynamic statement

create table test_dbcc(TraceFlag varchar(100),Status tinyint,Global tinyint,Session tinyint)insert into test_dbcc exec(‘DBCC TRACESTATUS‘)select * from test_dbcc

 

For dynamic SQL statements, or unconventional SQL statements like DBCC, you can save the result set in this way.

 

2. Insert exec statements cannot be nested.

2.1 In the following example, an error occurs when you try to save the result set of sp_help_job to a temporary table.

create table #JobInfo(job_id uniqueidentifier,originating_server nvarchar(128),name nvarchar(128),enabled tinyint,description nvarchar(512),start_step_id int,category nvarchar(128),owner nvarchar(128),notify_level_eventlog int,notify_level_email int,notify_level_netsend int,notify_level_page int ,notify_email_operator nvarchar(128),notify_netsend_operator nvarchar(128),notify_page_operator nvarchar(128),delete_level int,date_created datetime,date_modified datetime,version_number int,last_run_date int,last_run_time int,last_run_outcome int,next_run_date int,next_run_time int,next_run_schedule_id int,current_execution_status int,current_execution_step nvarchar(128),current_retry_attempt int,has_step int,has_schedule int,has_target int,type int)insert into #JobInfo exec msdb..sp_help_job

 

Returned error message: the insert exec statement cannot be nested.
MSG 8164, level 16, state 1, procedure sp_get_composite_job_info, line 72
An Insert exec statement cannot be nested.

Expand the stored procedure in the error message:

exec sp_helptext sp_get_composite_job_info

 

We found an insert... Exec nested call, SQL Server syntax does not support.

INSERT INTO @xp_results EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs, @job_owner, @job_id

 

 

2.2 you can use distributed queries to avoid this problem,This method was mentioned by the author in inside SQL Server 2005.
(1) first, open the server option ad hoc distributed queries.

exec sp_configure ‘show advanced options‘,1RECONFIGUREGOexec sp_configure ‘Ad Hoc Distributed Queries‘,1RECONFIGUREGO

 

(2) connect to the local machine through OpenRowSet, run the stored procedure, and obtain the result set
Use Windows Authentication

select * into #JobInfo_S1from openrowset(‘sqloledb‘, ‘server=(local);trusted_connection=yes‘,‘exec msdb.dbo.sp_help_job‘)select * from #JobInfo_S1

 

Use SQL Server Authentication

SELECT * INTO #JobInfo_S2FROM OPENROWSET(‘SQLOLEDB‘,‘127.0.0.1‘;‘sa‘;‘sa_password‘,‘exec msdb.dbo.sp_help_job‘)SELECT * FROM #JobInfo_S2

 

This method not only saves the trouble of Manual table creation, but also avoids the problem that insert exec cannot be nested. Almost all SQL statements can be used.

-- DBCC cannot directly run select. * Into # tfrom OpenRowSet ('sqloledb', '2017. 0.0.1 '; 'sa _ password', 'dbcc log (''master'', 3)') as a -- select. * Into # tfrom OpenRowSet ('sqloledb', '2017. 0.0.1 '; 'sa _ password', 'exec (''dbcc log ('''''master''', 3) ''')') as

 

01. Select into from the stored procedure result set to the temporary table