locked
track insertion of data in the database in sql RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    hi all

    i need to track data that is being inserted into the table of certain values

    for example, i have a question with 4 values, Excellent, Good, Average, Poor. now suppose i need to track the Number of times the Value "POOR" is inserted in database in 5 minutes, the Value and the time can be flexible.

    no idea on how to get this done

    any help would be appreciated.

    thanks

    Monday, September 3, 2012 5:01 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Take a look at  OUTPUT clause 

    create table itest ( i int identity not null primary key, j int not null unique )
    create table #new ( i int not null, j int not null)

    insert into itest (j)
    output inserted.i, inserted.j into #new
    select o.object_id from sys.objects as o

    select * from #new
    drop table #new, itest;
    go

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Monday, September 3, 2012 5:44 AM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote

    1.Create a job and schedule at every 5 minutes interval

    2. Use the below script in the step;(Assuming you know the DB mail configuration)

    If ((Select Count(1) From T1 Where Value = 'Bad' and InputTime <= GETDATE() 
    and InputTime>DATEADD(MINUTE,-5,GETDATE()))>=2)
Begin
	pRINT 'HI'
	--Send mail
End

    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!


    Monday, September 3, 2012 6:28 AM
    Answerer

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    This is much easier - Plz provide your table structure and sample data.

    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Monday, September 3, 2012 5:24 AM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote

    Take a look at  OUTPUT clause 

    create table itest ( i int identity not null primary key, j int not null unique )
    create table #new ( i int not null, j int not null)

    insert into itest (j)
    output inserted.i, inserted.j into #new
    select o.object_id from sys.objects as o

    select * from #new
    drop table #new, itest;
    go

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Monday, September 3, 2012 5:44 AM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote

    my table Structure is 

    RowID   Value   InputTime

       1           BAD       8/10/2012 1:17:00 PM

      2           GOOD    8/10/2012 1:17:50 PM

       3          BAD       8/10/2012 1:19:00 PM

    now i want to trigger a mail if my table receives 2 BAD values in 5 minutes

    Monday, September 3, 2012 6:12 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    1.Create a job and schedule at every 5 minutes interval

    2. Use the below script in the step;(Assuming you know the DB mail configuration)

    If ((Select Count(1) From T1 Where Value = 'Bad' and InputTime <= GETDATE() 
    and InputTime>DATEADD(MINUTE,-5,GETDATE()))>=2)
Begin
	pRINT 'HI'
	--Send mail
End

    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!


    Monday, September 3, 2012 6:28 AM
    Answerer