Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Append data to table after populating virtual table

Posted on 2006-05-31
9
Medium Priority
?
355 Views
Last Modified: 2012-05-05
I need to create a virtual table with the current date and time and append this date and time to a table to show when the query was last run. I am going to do this in a stored procedure to keep track of when the data was last run. if anyone can assist with this that would be great or if anyone knows a better approach please let me know.  Please be specific.

Thanks
0
Comment
Question by:earngreen
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16798721
>with the current date and time
well, that is the function getdate() in sql server

>stored procedure to keep track of when the data was last run.
create table track_procedure ( date_called datetime )


and in the procedure, do this insert:

insert into track_procedure (date_called) select getdate()
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16798736
Can you show the rest of the code of your stored procedure, and the setup of the destination table?

I would suggest simply adding a line of code in the stored procedure with something to this affect

INSERT INTO destinationTableName
(DateTimeLastRun, ProcedureName)
VALUES
(getdate(), 'This ProcedureName')
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16798762
yeah what angell said will work.. select getdate() is what I should have said instead of just getdate().. my apologies.
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 5

Accepted Solution

by:
bwdowhan earned 2000 total points
ID: 16798819
I use something like angelIII suggests to identify when and if stored procedures are run:

--Create LOG_TABLE
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LOG_TABLE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LOG_TABLE]
GO

CREATE TABLE [dbo].[LOG_TABLE] (
      [LOGID] [int] IDENTITY (1, 1) NOT NULL ,
      [SP_NAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [RUN_DATE] [datetime] NOT NULL
) ON [PRIMARY]
GO



--Create LOG_ENTRY Stored Procedure
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LOG_ENTRY]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[LOG_ENTRY]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROC LOG_ENTRY @V_SP_NAME VARCHAR(30)
AS
BEGIN

   INSERT INTO LOG_TABLE (SP_NAME,RUN_DATE) VALUES (@V_SP_NAME,GETDATE())

END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



--Use following in each stored procedure you want to log
   EXEC LOG_ENTRY 'MY_STORED_PROCEDURE_NAME'


0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16798859
I like that approach, bw.. You can modify that stored procedure and not have to open each stored procedure that you want logged (as long as you aren't changing the input paramaters)..

Very nice.
0
 

Author Comment

by:earngreen
ID: 16799094
I like the approaches that you have suggested. I need to log the date aand then use that date as a parameter in my where clause like below. bw in your example, can I just pull the information at that time from the log table.
here is an example of what I have thanks.

CREATE PROCEDURE

SELECT DISTINCT emp.empid,emp.first,emp.last,emp.hiredate,hr.value from emp inner join emp.empid = hr.emdpid
where emp.hiredate between lastrundate(logdate) and today
0
 
LVL 5

Expert Comment

by:bwdowhan
ID: 16801252
You could query the LOG_TABLE to retrieve the last run_date of the stored procedure you are in and store the result in a variable to use in your query:

Before you:
 EXEC LOG_ENTRY 'MY_STORED_PROCEDURE_NAME'

You would get the last run date with something like this:

DECLARE @LAST_RUN Datetime

SET @LAST_RUN = (SELECT MAX(RUN_DATE) FROM LOG_TABLE WHERE SP_NAME = 'MY STORED_PROCEDURE_NAME')

In your query you would just say:

SELECT DISTINCT emp.empid,emp.first,emp.last,emp.hiredate,hr.value from emp inner join emp.empid = hr.emdpid
where emp.hiredate >= @LAST_RUN



0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16957041
point split
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question