How to put the result set to a temporary table in SQL Server

　A. Select into  1.  uses SELECT INTO to automatically generate temporary tables without the need to create   SELECT * into #temp the from sysobjects  01.  save Stored procedure result set SELECT INTO to Temp table   SELECT * FROM #temp   2.  if a temporary table with the same name already exists in the current session   SELECT * into #temp from SYSOBJEC ts  run again, you will be prompted with an error: The name   '%s already exists in the database '   objects.   MSG 2714, level 16, state 6, line 2  There is already a object named ' #temp ' in the database .  before using SELECT INTO, you can judge:  if object_id (' tempdb. #temp ')  is not null  drop table #temp   SELECT * to #temp from sysobjects  select * from #temp &nbs　　P 3.  using SELECT INTO to generate an empty table   if you want to generate an empty table structure that contains no data, you can give a constant inequality as follows:  SELECT * into #temp from sysobjects where 1=2   SELECT * from #temp   two. Insert into  1.  using INSERT INTO, you need to manually create a temporary table   1.1  Save the node returned from the SELECT statement Fruit set   CREATE TABLE Test_getdate (c1 datetime)   INSERT INTO test_getdate select GETDATE ()   SELECT * FROM tes t_getdate  1.2 Save result set returned from stored procedure   CREATE table #helpuser   UserName nvarchar (, ) RoleName nvarchar (+),  LoginName nvarchar (+),  defdbname nvarchar (+),  defschemaname nvarchar (+),  UserID smallint,  SID SMA llint )   INSERT INTO #helpuser exec sp_helpuser  select * from #helpuser   1.3  Save the result set returned from the dynamic statement &　　nbsp CREATE TABLE test_dbcc  traceflag varchar,  Status tinyint,  Global tinyint,  Session tinyint  )   INSERT into TEST_DBCC exec (' DBCC TRACESTATUS ')   SELECT * from test_dbcc  for dynamic SQL, or similar to DBCC SQL statements, you can save the result set in this way.   2.  cannot be nested using the INSERT EXEC statement   2.1  The following example, trying to save a sp_help_job result set to a temporary table, an error occurred   CREATE TABLE #JobInfo &　　nbsp job_id uniqueidentifier,  originating_server nvarchar (+),  name nvarchar (+),  enabled tinyint,&　　nbsp Description nvarchar (, ) start_step_id int,  category nvarchar (+),  owner nvarchar (128),  Notify_level_eventlog int,  notify_level_email int,  notify_level_netsend int,  Notify_level_pa GE int ,  notify_email_operator nvarchar (+),  notify_netsend_operator nvarchar (+),  Notify_　　Page_operator nvarchar ($),  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_EXECU Tion_step nvarchar ($),  current_retry_attempt int,  has_step int,  has_schedule int,  has_target int,  type int )   INSERT into #JobInfo exec msdb: sp_help_job  return error message: INSERT exec  statement cannot be nested.   MSG 8164, level 16, state 1, procedure sp_get_composite_job_info, line 72  an INSERT EX EC statement cannot be NESTED.&NBSp Expand the stored procedure in the error message:  exec sp_helptext sp_get_composite_job_info  found there's an insert into ... EXEC nested invocation, SQL Server is not syntactically supported.   INSERT into @xp_results   EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs,  @job_ Owner, @job_id   2.2  can use distributed queries to avoid this problem, as the author mentions in inside SQL Server 2005   (1) First to open the server option AD HOC distributed queries  exec sp_configure ' show advanced options ',1  reconfigure  go  exec sp_configure ' Ad Hoc D istributed Queries ',1  reconfigure  go  (2) Connect to native via OPENROWSET, run stored procedures, get result set   Use Windows authentication   SEL ECT * into #JobInfo_S1   from OPENROWSET (' SQLOLEDB ',  ' server= (local); Trusted_connection=yes ', ' exec Msdb.dbo.sp_help_job ')   SELECT * from #JobInfo_S1   Using SQL Server Authentication   SELECT * into #JobInfo_S2   from OPENROWSET (' SQLOLEDB ', ' 127.0.0.1 '; ' sa '; ' sa_password ', ' exec msdb.dbo.sp_help_job ')   SELECT * from #JobInfo_S2   This way, it eliminates the hassle of manually building a table, or prevents insert exec  from nestingThe problem. Almost all SQL statements can be used.  --DBCC cannot run directly   SELECT a.* into #t   from OPENROWSET (' SQLOLEDB ', ' 127.0.0.1 '; ' sa '; ' Sa_password ',  ' d Bcc log (' master ', 3) ')  as a --can be adapted   SELECT a.* into #t   from OPENROWSET (' SQLOLEDB ', ' 127.0.0.1 '; ' sa '; ' Sa_password ',  ' exec (' DBCC LOG (' ' ', ' ' ' ' ' "', ' 3 ') ')  as a

How the result set is placed into a staging table in SQL Server