How to insert a record every nth minutes 1

[bslintx]

Hi all,

I am very new to mssql server and would appreciate so input on my problem.

SCENARIO: I have a database that contains numbers that need to be recorded during a particular time span. A start and end. In a nutshell, these are personnel accountability numbers. Meaning, how many folks were in a particular department at such and such time. These numbers are manually inserted via html form by section heads...at any given time.


FIX ACTION:
What I thought about doing was taking advantage of a DTS/trigger package to run in 1 minute intervals. Ultimately, I'd like to do this 24/7 so "the boss" will not actually have to initiate a start time because it's pretty close to real-time, being at 1 min a piece. However, I am afraid this may be too much strain because let's face it, a lot of recs will be inserted in a 24 hour period. Any "food for thought" ideas on this? I am sure this is doable; however, is it bad design?

PROCESS:
A user inputs a set of numbers into database on the frontend

At the backend...SQL copies recs from accountability table then inserts into a archive table...allowing to produce a timeline. This timeline will obviously go up and down and should show accountability lows and peaks

My apologies for the length here. Am I going in the right direction here? Or is there something more adequate?

BTW, I have it working on the client side using javascript; however, if you drop the screen....the insert sql statement is to no use.

Your time and input is greatly appreciated

Brian

 

[SQLDenis]

put the sql statement in a job, schedule the job to run every minute and you are done

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Jobs can be added under Enterprise Manager-->Managment-->SQL Server Agent-->Jobs

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bslintx]

Thanks Denis.

I tried that earlier and I guess I have an invlaid sql statement. Can you run 2 at one time? Because I need to select from one table then insert into another?

SELECT     SUM(Assigned) AS assigned, SUM(Present) AS present, SUM(Leave) AS leave, SUM(TDY) AS tdy, SUM(Appointment) AS appointment, SUM(Quarters) 
                      AS quarters, SUM(Other) AS other
FROM         tblUccRecallInputs
                          INSERT      
                           INTO            tblArchives(WorkDate, Assigned, Present, Leave, TDY, Appointment, Quarters, Other, SnapTime)
VALUES     ('" & Date & "', [ & assigned & ], [ &  present & ], [ & leave & ], [ & tdy & ], [ & appointment & ], [ & quarters & ], [ & other & ], '" & time & "')

- I parsed on original scheduling and got no errors.
- I see it trying to run but fails
- I tried to run the code in query analyzer ..passes sntax but errors on

' & assigned & ' is not permitted in this context

I am obviously trying to get the select sum and use it as a var in the insert...I am getting confused because it passes syntax...but I may be getting it confused with my asp syntax...please advise...and thanks

 

[SQLDenis]

try this

INSERT INTO tblArchives(WorkDate, Assigned, Present, Leave, TDY, Appointment, Quarters, Other, SnapTime)
SELECT    GETDATE(), SUM(Assigned) AS assigned, SUM(Present) AS present, SUM(Leave) AS leave, SUM(TDY) AS tdy, SUM(Appointment) AS appointment, SUM(Quarters) 
                      AS quarters, SUM(Other) AS other,GETDATE()
FROM         tblUccRecallInputs

BTW what is the difference between WorkDate and SnapTime I used getdate() for both

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bslintx]

Thanks Denis....worked w/o a glitch. I need to ask your advice. Is this too much or rather overkill? I am assumming I can add a start/stop time where any rec not in this between sql statement will be deleted to save room. I am worried about the MANY MANY recs that will be inserted. Hmmm, perhaps only insert if a change? Any thoughts?

Thanks!

 

[bslintx]

Sorry...just realized you asked a question.

WorkDate is the current date and Snap time is the time the rec was inserted into timeline...to ultimately create a snapshot...so if someone wanted to see in between this time and this time on where the date is this...then they can do this

 

[SQLDenis]

Depends on your reporting requirements
If the business wants every minute then do it, if not run it every 5 minutes maybe
you can also keep a duplicate of the table that you populate with additional data once a night and do your aggregations from that table (seperate server of course, more like a data warehouse scenario)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bslintx]

Perfect Denis....thnks very much.

Have a good one