select query for only changed rows

Question

select query for only changed rows

yaman bas

SSC-Addicted

Points: 498
More actions
January 15, 2010 at 4:00 pm

#94413

hi,
forexample i have a stock table on mobile device sqlce which got from an sql 2000 database by a select query, i need to get only the changed rows on sql2000 table to my mobile device. maybe the price changed, or stock quantity or maybe a new stock inserted. i don't want to delete table on sqlce and get all data from sql 2000 again.How may i do it??

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

José.Cruz Right there with Babe Points: 722 More actions · Answer 1

Hi there,

I would create a DateTime NOT NULL column, ChangeDate on the Sql 2000 table with a getdate() default, and update this column, with a trigger whenever an update is performed in the record.

On the SqlCe side i would have also this column, so whenever the SqlCe needs to check for changes, it could:

1- get de MAX(ChangeDate) from the SqlCe table

2- get records from Sql 2000 that where modified after that date

José Cruz

Garadin One Orange Chip Points: 29613 More actions · Answer 2

If you don't need to know the time the record changed, you just need to know *that* it changed, a timestamp column will serve the same purpose without the trigger.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Even that may not do it... you can update a row with the same data as what it started with and the TimeStamp will still change.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Garadin One Orange Chip Points: 29613 More actions · Answer 4

This is true. It will depend on your actual situation as to which is the more efficient method. It might be that copying a few extra rows that haven't *really* changed, only had the same data re-written, is a less expensive option than checking the status of every field against what it used to be.

I made a guess based on the synching to a mobile device that a timestamp check would be the cheaper option; as if you want to check every field you have to actually transmit the entire row one way or the other to check the values and see what you actually need to copy. That's potentially a lot of extra traffic.

On the other hand, if the bandwidth isn't as big an issue as the storage space or making sure you have only the rows that have truly new information, checking every field is the way to go. I guess the question is, what defines a change? Is it when either the mobile device or the server has changed and the two are no longer equivalent?

You could also have triggers on both databases that checked for when a value was changed and then update a field that was checked for differences between the two, but you're still looking at having to do an additional comparison to see if the same field was changed on both databases to be the same value, at which point the row is no longer a 'changed row'.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

The original post seems to suggest that this is all for a staging table upsert into a main table (or maybe I've not had enough coffee). I'd recommend a simple multicolumn inner join to mark (as a scheduled job) all of those things in the staging table that are different. It's a single pass, no need of triggers, etc. If the target table has a small PK (1 or just a couple of columns), then the "mark" should be with an entry from the PK to make the join for the update very easy. Anything not marked in such a fashion must be "new" and in need of an INSERT with no join.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Garadin One Orange Chip Points: 29613 More actions · Answer 6

I just re-read it and I think you're right. *I'm* working with 2 way synchronization between a mobile device/server so I think I jumped to that conclusion a bit too quickly =).

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

yaman bas SSC-Addicted Points: 498 More actions · Answer 7

thank's for the answers, i tried updated_date_time column also there was in the table and that's okay. But i wished that may be there was a word for the SELECT query about this issue. Because i would guess that SQL Database Log file is saving the transactions and if it saves we may Know the last updates on the tables.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

Why would the log file know anything about the relationships between a staging table and a primary table?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)