Link to home
Start Free TrialLog in
Avatar of kdunnett
kdunnett

asked on

Save result of sp_who in a table with inserted date

All,

I created a table that matches the structure of sp_who:

table tbl_sp_who:
  spid            smallint,
  ecid            smallint,
  status            nchar(30),
  loginame            nchar(128),
  hostname      nchar(128),
  blk            char(5),
  dbname            nchar(128),
  cmd            nchar(16)

When you execute the below insert statement:
insert into tbl_sp_who execute sp_who

It saves the result into the table...

NOW, I want to add a column "insertDate" where when a record goes in, it has a time stamp of when it goes it.  This way I can look at the table like a log.

How do I do it?  I tried adding the extra column and going the 'getDate()' route but just adding the extra column crashes the insert statement.  

Any ideas?

Thanks in advance,
kris
ASKER CERTIFIED SOLUTION
Avatar of Nightman
Nightman
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
you create the column with the default detdate(), but then don't specify it in the values list.
Avatar of kdunnett
kdunnett

ASKER

Thanks!

Kris