store select result in a table

Asked

Viewed 113 times

0

Good afternoon

I have the following query

SELECT
 +cast((DATEDIFF(SECOND,r.start_time,GETDATE())/60)%60 as varchar)+'m '
 duracao
 ,isnull(s.login_name, s.original_login_name) AS LOGIN
 ,r.session_id
 ,r.wait_time
 ,r.cpu_time
 --,r.total_elapsed_time
 --,r.logical_reads
-- ,r.row_count
 --,r.blocking_session_id
 --,r.wait_resource
-- ,pp.lastwaittype
-- ,pp.status
 ,sql.TEXT
 FROM sys.dm_exec_requests r
 JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
 OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) sql
 OUTER apply (select max(p.lastwaittype) as lastwaittype, min(p.status) as status  from sys.sysprocesses p where p.spid = r.session_id ) pp
 WHERE 1 = 1
  and r.database_id NOT IN (1,0)
  AND cast((DATEDIFF(SECOND,r.start_time,GETDATE())/60)%60 as varchar) > 10
  order by 1 desc

And a table called coleta_query, I would like to store the result of this query in my table, someone would know?

sql sql-server

  • The table coleta_query has the same columns as the result of the query?

  • INSERT INTO you_table (you_field list) you_command_select

  • What is the point of you putting in your clause WHERE 1 = 1 and ? Is it possible that such clause will result in false results?

1 answer

1

A simple solution is to encapsulate the query SQL code in a common table expression (CTE, common table Expression) and use the INSERT statement to include the query result in the table coleta_query.

-- código #1
with Consulta as (
SELECT ...
  from ...
  where ...
)
INSERT into coleta_query (duracao, login, session_id, wait_time, cpu_time, [text])
  SELECT duracao, login, session_id, wait_time, cpu_time, [text]
    from Consulta;

Remove the ORDER BY clause from the original code of your query.

Enter the column names of the query table in the INSERT statement; for demonstration I used the same names as the original SQL query.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.