CODEWITHSUNDEEP
sql-server
sunsets
sunsets

Reputation: 411

Getting only new data from SQL Server

I've run my application which is getting data from SQL Server periodically. The problem here is that I want to get the records created since the last read.

Let me show an example.

At first, (at 12:00:00)

Table
---------
orderId orderName
1       name1
2       name2
3       name3
4       name3

I'm going to select all data here.

SELECT *
FROM TABLE

After one minute, some data was added into TABLE like below,

Table
---------
orderId orderName
1       name1
2       name2
3       name3
4       name3
5       name4
6       name5

At this point, when I select the data like below,

select *
from TABLE

what I want to get is row no. 5 and row no.6, which is added after I selected before.

My idea is I need to keep an identifier which indicates the transaction of id or something like that but I still don't catch any idea... Can you help me? Any keyword or links helping me?

It would be helpful for me to implement the refresh event to get only new data with the idea I'm expecting.

Thank you guys, here's the more specific table I have.

Table
---------
orderId      orderTime
1       2017-9-4 8:00:00.000
1       2017-9-4 8:00:10.000
1       2017-9-4 8:00:20.000
1       2017-9-4 8:00:30.000
1       2017-9-4 8:00:40.000
1       2017-9-4 8:00:50.000

2       2017-9-4 8:00:11.000
2       2017-9-4 8:00:20.000
2       2017-9-4 8:00:32.000
2       2017-9-4 8:00:40.000

And the time records will be added continuously based on the orderId, respectively. Most idea that I keep the last orderId may not work for my case. How do I handle it in the specific this case?

Upvotes: 1

Views: 2272

Answers (4)

MatSnow
MatSnow

Reputation: 7527

Store the last orderId and the associated last orderTime in a variable.
This should work as you want.

DECLARE @lastOrderId int = 2
DECLARE @lastOrderTime datetime = '2017-9-4 8:00:40.000'

SELECT * FROM Table
WHERE (orderId = @lastOrderId AND orderTime > @lastOrderTime) OR orderId > @lastOrderId
ORDER BY orderId, orderTime;

Upvotes: 0

Nick.Mc
Nick.Mc

Reputation: 19204

I see a lot of vague suggestions for saving in sessions, variables etc. I suggest you just use the table itself:

Find the last key in the local table:

SELECT ISNULL(MAX(OrderID),0) FROM TABLE

Use that to decide what to select out from your remote table

DECLARE @MaxID INT
SELECT @MaxID = ISNULL(MAX(OrderID),0) FROM TABLE


INSERT INTO TABLE (OrderID, Col1, Col2)
SELECT OrderID, Col1, Col2
FROM REMOTETABLE
WHERE OrderID > @MaxID

I see you have a recent edit saying this may not work. You need to explain why otherwise no one has any chance of helping. Is it because the OrderID might not always increment? Can it go backwards? You need to explain.

Upvotes: 0

shambhu yadav
shambhu yadav

Reputation: 251

There not any keyword to identify latest entry of last execution. Because, your query run both the time on different session. So, you can't identify it.

But Basically, we have other option to do this task.

  1. Do it with Maintaining Max value of identifier. Maintain it into the variable, Session, Parameter, etc...

    select * 
from table 
where orderId > yourvariableValue

Upvotes: 1

Lajos Arpad
Lajos Arpad

Reputation: 76814

You need to always save somewhere the largest orderId you've got at your last select and then, knowing that orderId, use it in your select, like this:

select ...
from ...
where orderId > 4

Upvotes: 0

Related Questions