Insert result of sp_who into table multiple times with timestamp

Question

This question SQL Server: Filter output of sp_who2 has a method to insert the result of sp_who2 into a table.

How can I insert the result into a table in addition to a timestamp for the insert.

drop table if exists #sp_who2; 
CREATE TABLE #sp_who2 
(

   SPID INT,  
   Status VARCHAR(1000) NULL,  
   Login SYSNAME NULL,  
   HostName SYSNAME NULL,  
   BlkBy SYSNAME NULL,  
   DBName SYSNAME NULL,  
   Command VARCHAR(1000) NULL,  
   CPUTime INT NULL,  
   DiskIO INT NULL,  
   LastBatch VARCHAR(1000) NULL,  
   ProgramName VARCHAR(1000) NULL,  
   SPID2 INT,
   te4xt varchar(250)
) 
GO

INSERT INTO #sp_who2
EXEC sp_who2
GO

GMB · Accepted Answer · 2020-12-21T22:19:32.867

2

You can add a datetime column that defaults to the insert date to the table. This requires enumerating the target columns for insert:

CREATE TABLE #sp_who2 (
   SPID INT,  
   Status VARCHAR(1000) NULL,  
   Login SYSNAME NULL,  
   HostName SYSNAME NULL,  
   BlkBy SYSNAME NULL,  
   DBName SYSNAME NULL,  
   Command VARCHAR(1000) NULL,  
   CPUTime INT NULL,  
   DiskIO INT NULL,  
   LastBatch VARCHAR(1000) NULL,  
   ProgramName VARCHAR(1000) NULL,  
   SPID2 INT,
   te4xt varchar(250),
   dt_created DATETIME2(3) DEFAULT (SYSDATETIME())
);

-- enumerate all columns but the new one here
INSERT INTO #sp_who2 (SPID, Status, ..., te4txt)
EXEC sp_who2

edited Dec 21 '20 at 22:19

answered Dec 21 '20 at 22:05

GMB

216,147
25
84
135

Thanks I guess that was what I was looking for! What do you mean with SP code? – xhr489 Dec 21 '20 at 22:06
Ahh... I want to take everything and not only for a specific session – xhr489 Dec 21 '20 at 22:07
it says `column name or number of supplied values does not match table definition.` – xhr489 Dec 21 '20 at 22:08
1

@xhr489: ok. The SP must be doing something like `insert into #sp_who2 values (...)`, while it would need to be: `insert into #sp_who2(spid, status, login, ...) values (...)`. If you want the new column, then you will need the code of the SP. – GMB Dec 21 '20 at 22:11
1

Yes `insert into #sp_who2(spid, status, login, ...) EXEC sp_who2` worked thanks! – xhr489 Dec 21 '20 at 22:16

Insert result of sp_who into table multiple times with timestamp

1 Answers1