insert row if one doesnt exist since x minutes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

insert row if one doesnt exist since x minutes

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2005-03-01 : 09:07:20

Hi,

I know how to do this using code, but I figured I could save 1 db call if I can figure this out using sql-script!

Problem:
I am inserting a row into the database with a timestamp. I don't want to insert a row if there was already a row inserted x minutes ago, where x is the a variable number of minutes that i'm going to pass into the stored procedure as an integer value (number of minutes).

IF EXISTS(SELECT * FROM TABLE_1 WHERE ?????myTimeStamp???? )
BEGIN
INSERT (.....)
END

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2005-03-01 : 10:28:44

would I use datediff?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-01 : 10:35:31

you'd still need a check...

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-01 : 10:39:17

Are you using a timestamp datatype? Becuase it's not really a date/time, I'm not sure if you can do this. Unless you are using a datetime datatype, but just calling the column "timestamp".

- Jeff

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2005-03-01 : 10:43:17

Jeff, sorry about that!
Yes indeed it is a DateTime type.

please advice....

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-01 : 10:54:06

Something like



SET NOCOUNT ON
DECLARE @MinutesAgo int, @DateAgo datetime
SELECT @MinutesAgo = 2, @DateAgo = DATEADD(mi,@MinutesAgo*-1,GetDate())
SELECT DATEDIFF(mi,@DateAgo,GetDate()), @DateAgo, GetDate(), 'Not Yet'

WHILE DATEDIFF(mi,@DateAgo,GetDate()) < = 2
  BEGIN
	WAITFOR DELAY '000:00:15'
	SELECT DATEDIFF(mi,@DateAgo,GetDate()), @DateAgo, GetDate(), 'Not Yet'
  END
SELECT DATEDIFF(mi,@DateAgo,GetDate()), @DateAgo, GetDate(), 'OK, Everyone in the pool'
SET NOCOUNT OFF

Brett

8-)

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2005-03-01 : 11:04:03

Brett,

Thanks. I don't want the insert to wait. If it hasn't been longer than x minutes, just don't do anything.

PW
Yak Posting Veteran

95 Posts

Posted - 2005-03-01 : 11:13:51

Assuming you're passing a bunch of constant values as @variables into your stored procedure as the inserted column values ...

Insert Into YourTable
(<columns>)
Select <@constants>
Where Not Exists (
Select *
From YourTable
Where KeyColumn1 = @constant1
And <other key parts if required>
And DateDiff(n, @YourMinutesParam, YourTable.Timestamp) > @YourMinutesParam
)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-01 : 11:16:01

PW beat me to it by a few minutes... but ill post it anyway


declare @t table (id int, dt datetime)
insert into @t
select 1, '2005-03-01 16:00:00' union all
select 2, '2005-03-01 16:05:00' union all
select 3, '2005-03-01 16:10:00' union all
select 4, '2005-03-01 16:15:00' union all
select 5, '2005-03-01 17:00:00'

declare @timespan int
set @timespan = 10 -- interval in minutes to check in. if the record is in that interval don't insert values

insert into @t(id, dt)
select 6, getdate()
from 
(
select 	max(dt) as dt
from 	@t
) t1
where 	datediff(n, t1.dt, getDate()) > @timespan

select 	* 
from 	@t

Go with the flow & have fun! Else fight the flow

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-01 : 11:17:31

quote:
Originally posted by sql777

Brett,

Thanks. I don't want the insert to wait. If it hasn't been longer than x minutes, just don't do anything.

The loop is just an example.

Just change the while to an existance check.

If you cut and paste the code in to QA you can see a sample of what it's doing.

You can then just modify it to your needs.

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-01 : 11:30:54


USE Northwind
GO

CREATE PROC mySproc99
	@MinutesAgo int, @Date datetime
AS
SET NOCOUNT ON
DECLARE @DateAgo datetime
SELECT @DateAgo = DATEADD(mi,@MinutesAgo*-1,@Date)
IF DATEDIFF(mi,@DateAgo,GetDate()) > @MinutesAgo
  BEGIN
	SELECT 'Data IS greater than ' + CONVERT(varchar(10),@MinutesAgo) + ' Minutes Old'
	-- PERFORM YOUR WORK
  END
ELSE
  BEGIN
	SELECT 'Data IS NOT greater than ' + CONVERT(varchar(10),@MinutesAgo) + ' Minutes Old'
	-- PERFORM YOUR WORK
  END

SET NOCOUNT OFF
GO

DECLARE @x datetime
SELECT @x = DATEADD(mi,0,GetDate())
EXEC mySproc99 2, @x 

SELECT @x = DATEADD(mi,-2,GetDate())
EXEC mySproc99 2, @x 
GO

DROP PROC mySproc99
GO

The other examples give to you are to be able to perform them as a set operation...I believe you're looking for one row at a time....

Brett

8-)

sstrangee
Starting Member

4 Posts

Posted - 2005-03-01 : 15:39:38

Why would you set up a loop in a stored proc like that?

Why not write the stored proc and then setup a SQL Job to run every 2 mins or something?

Just my thought...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources