Stored Procedure - Insert into table

• ramsfield

  SSC-Addicted

  Points: 411

  More actions

  January 18, 2006 at 2:35 pm

  #91384

  I have the following stored procedure. I would like it to take all the results and insert them into a table. The current script only inserts one result. How can I make it return and insert all the results? If I just run the select statement, without the variables, I receive about 30 rows.

  CREATE PROCEDURE [sp_logged_in_users]

  AS

  DECLARE @login_name2 [varchar] (10)

  DECLARE @database_name2 [varchar] (15)

  DECLARE @login_time2 [datetime]

  DECLARE @last_batch2 [datetime]

  BEGIN

  SELECT @login_name2 = master.dbo.sysprocesses.loginame,

  @database_name2 = master.dbo.sysdatabases.name,

  @login_time2 = master.dbo.sysprocesses.login_time,

  @last_batch2 = master.dbo.sysprocesses.last_batch

  FROM master.dbo.sysprocesses INNER JOIN

  master.dbo.sysdatabases ON master.dbo.sysprocesses.dbid = master.dbo.sysdatabases.dbid

  WHERE (master.dbo.sysdatabases.name = N'Tidemark')

  INSERT INTO [Tidemark].[dbo].[logged_in_users]

  ( [Login_Name],

  [DBName],

  [LoginTime],

  [Lastbatch])

  VALUES

  ( @login_name2,

  @database_name2,

  @login_time2,

  @last_batch2)

  END

  GO

• Jules Bonnot

  SSCertifiable

  Points: 5488

  More actions

  January 18, 2006 at 2:40 pm

  #615632

  Not sure why you are using variables these can only hold one value hence will only insert one row.

  INSERT INTO [Tidemark].[dbo].[logged_in_users]

  ( [Login_Name],

  [DBName],

  [LoginTime],

  [Lastbatch])

  SELECT  master.dbo.sysprocesses.loginame, 

   master.dbo.sysdatabases.name,

  master.dbo.sysprocesses.login_time, 

  master.dbo.sysprocesses.last_batch

  FROM master.dbo.sysprocesses INNER JOIN

  master.dbo.sysdatabases ON master.dbo.sysprocesses.dbid = master.dbo.sysdatabases.dbid

  WHERE (master.dbo.sysdatabases.name = N'Tidemark')

  That should do it. This code is not tested.

  Jules

  www.sql-library.com[/url]

• ramsfield

  SSC-Addicted

  Points: 411

  More actions

  January 18, 2006 at 2:49 pm

  #615636

  That worked! After you mentioned it, the variable to one value issue made perfect sense.

  Thanks

• Anthony Robinson

  SSC Veteran

  Points: 240

  More actions

  January 25, 2006 at 9:40 am

  #616956

  This is similar to an issue I'm encountering...but I'm trying to insert the results of an EXEC SP_SPACEUSED into a table...

   

  Any suggestions?

   

  Thanks!!

   

• Fred Williams-134192

  SSCrazy

  Points: 2482

  More actions

  January 25, 2006 at 11:23 am

  #616992

  use pubs

  go

  create table #junk

  (

  Name nvarchar(20),

  Rows char(11),

  reserved varchar(18),

  Data varchar(18),

  index_size varchar(18),

  Unused varchar(18)

  )

  insert #junk exec sp_spaceused @objname = 'authors'

  insert #junk exec sp_spaceused @objname = 'titles'

  select * from #junk