Fetch only modified and newly inserted records by using the view

hi ,

 i am using a view which fetches some lacs of records. i need to run  this view at regular intervals of time i.e. for every one hour to send the data to other data base.
 there are 3 base tables in that view.all these base table are having timestamp field.

 now, it is becoming very difficult to fetch lacks of records for every one hour. so i want to modify this view. now i want to fetfch only modified records or newly inserted records in the last one hour.
 
 is this possible by using the timestamp field of base tables and the view only or is there any better way.

the simple form of my view looks like this

CREATE  View [dbo].[GetData]  AS
select std.studentid,
            std.studentsname,
            dept.deptname,
            m.subject
from student std,department dept,marks m
where std.deptid = dept.deptid
      and std.studentid = m.studentid

thans in advance.
gpinfotechAsked:
Who is Participating?
 
gpinfotechAuthor Commented:
Hi thank you for reply
0
 
puranik_pCommented:
Assuming that you have a datetime field for the timestamp, you can use something like this...

CREATE  View [dbo].[GetData]  AS
select std.studentid,
            std.studentsname,
            dept.deptname,
            m.subject
from student std,department dept,marks m
where std.deptid = dept.deptid
      and std.studentid = m.studentid
      AND DATEDIFF(hour,yourtimestampcolumn,GETDATE()) <= 1

Open in new window

0
 
puranik_pCommented:
forgot that the timestamp is present in all three tables. Modified below...
CREATE  View [dbo].[GetData]  AS
select std.studentid,
            std.studentsname,
            dept.deptname,
            m.subject
from student std,department dept,marks m
where std.deptid = dept.deptid
      and std.studentid = m.studentid
      AND (DATEDIFF(hour,yourtimestampcolumninstd,GETDATE()) <= 1
           OR DATEDIFF(hour,yourtimestampcolumnindept,GETDATE()) <= 1
           OR DATEDIFF(hour,yourtimestampcolumninm,GETDATE()) <= 1)

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Bhavesh ShahLead AnalysistCommented:
Hi,

if you talking  about modified in last 1 hour then you can do this way.

CREATE  View [dbo].[GetData]  AS
select std.studentid,
            std.studentsname,
            dept.deptname,
            m.subject
from student std,department dept,marks m
where std.deptid = dept.deptid
      and std.studentid = m.studentid
and std.TimeStamp >= DATEADD(HH,-1,getdate())


- Bhavesh

0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

To improve performance, use index on table.
That will improve performance.

0
 
gpinfotechAuthor Commented:
hi puranik,
 
 here timestamp  field is not the datetime data type field. it's data type itself is timestamp used to track the row version of each row.

 
0
 
puranik_pCommented:
The timestamp field given by SQL Server has got no relation to 'time'. So you will need to add another column of DATETIME type and update it every time a record is updated.
0
 
gpinfotechAuthor Commented:
yes, that's why i am stressing on it. timestamp field gets updated every time ,if the row modified. so based on this can we do anything.
0
 
puranik_pCommented:
If we want to stick to the timestamp column then do you have the last retrieved value when you call it next time? If you do, then we can write a procedure instead of the view which accepts the last timestamp as a parameter and returns rows where timestamp > lastvalue
0
 
gpinfotechAuthor Commented:
here all 3 tables are having lastupdatedtime fields of data type datetime. but problem is these are large tables each one having more than 200 columns and some n numbe of aprocedures modify the data in this table. so it's difficult now to check whether these  lastupdatedtime fields are updating at every modification or not. in some cases directly backend updations also goes on. that's why i have no option except the timestamp field.
0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

which database you are putting data?
is it sql server?

how you are putting data?

- Bhavesh
0
 
gpinfotechAuthor Commented:
so it's not possible to achive this by using only view.
 at present as i am using view ,so is there any way to store the last retrived value
0
 
gpinfotechAuthor Commented:
bavesh,
 
 ya it is sql server 2005
 
0
 
puranik_pCommented:
ok. How about the procedure below? You will need to store the OUTPUT value of the timestamp after every call and apss the same value when you call it next time. This will give you the incremental results.
CREATE PROCEDURE getdata
@rowversion ROWVERSION OUTPUT
AS
BEGIN
select std.studentid,
            std.studentsname,
            dept.deptname,
            m.subject
from student std,department dept,marks m
where std.deptid = dept.deptid
      and std.studentid = m.studentid
      AND 
      (timestampinstd > @rowversion
       OR timestampindept > @rowversion
       OR timestampinmark > @rowversion)

-- 
SELECT @rowversion = MAX(timestamp) FROM student WHERE timestamp > @rowversion
SELECT @rowversion = MAX(timestamp) FROM department WHERE timestamp > @rowversion
SELECT @rowversion = MAX(timestamp) FROM mark WHERE timestamp > @rowversion
END

Open in new window

0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

you are looking for updated data also or only inserted data?
0
 
gpinfotechAuthor Commented:
hi Brichsoft,

  both updated and inserted
0
 
John ClaesSenior .Net Consultant & Technical AnalistCommented:

If you can't do something because of the many app's working directly upon the db I suggest the folowing ::

Add th column Modified_dt DateTime
You can make a Trigger that updates the Field Modified_dt for every update of teh row with the getdate() function .

then you can use the view of puranik_p

This way you don't have to change all the other apps and you still have the column


regards
0
 
Alpesh PatelAssistant ConsultantCommented:
Please add columns like CreatedBy, CreatedDate, UpdatedBy, UpdatedDate

Based on the date filed you can get the newly added and updated rows.
0
 
gpinfotechAuthor Commented:
Thanks for the given solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.