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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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:
Hi thank you for reply
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gpinfotechAuthor Commented:
Thanks for the given solution
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.