How can I query a SQL database to find only the records that are new or changed since the last query?

Question

I'm a SQL newbie that got this project dumped in my lap. I'm reading SQL in a Nutshell and SQL Cookbook as quickly as I can, but if someone could point me in the right direction, that would be appreciated. I don't even know conceptually how to do this, so I don't know where to start.

I'm doing an extract from a private MSSQL database and writing it to a public (web) MySQL database. There are no fields in the private DB to indicate when something was added or updated. Does the SQL server know what's been added or changed, and can I find out somehow?

Thanks in advance for your time.

Answer 1

Adding a timestamp column as another responder suggests is the way to go. Then add insert and update triggers to populate the column automatically. Once that's in place, a simple query can return any row that's been added or changed since a particular time, within a specified period of time or other such criteria.

Answer 2

By default, no, there are no timestamps. So set one up! SQL Server has a timestamp field type which goes up as the rows are updated. This type is not an actual time, it's actually a row version. According to MS is' Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime2 data type.". Perfect.



http://www.sqlusa.com/articles2005/rowversion/ has some info on using it. Experiment with it, it should do what you want.

Answer 3

Doing this in sq. on my own could be a hellacious job - doing so in a script to break up the distinctive projects could be lots easier. Getting modern and previous month/3 hundred and sixty 5 days: decide on MONTH(currentdate()), 3 hundred and sixty 5 days(currentdate()) INTO @currentmonth, @currentyear; IF @currentmonth = a million THEN @priormonth = 12 @prioryear = @currentyear -a million ELSE @priormonth = @currentmonth -a million @prioryear = @currentyear; then you definitely can do your substantial question like this: decide on ReferenceNumber, stability, fee, "New" FROM someTable c the place MONTH(Date) = @currentmonth AND 3 hundred and sixty 5 days(Date) = @currentyear and not EXISTS (decide on a million FROM someTable p the place c.ReferenceNumber = p.ReferenceNumber AND MONTH(Date) = @priormonth AND 3 hundred and sixty 5 days(Date) = @prioryear) UNION decide on c.ReferenceNumber, c.stability, c.fee, "bigger" FROM someTable c, someTable p the place c.ReferenceNumber = p.ReferenceNumber AND MONTH(c.Date) = @currentrmonth AND 3 hundred and sixty 5 days(c.Date) = @currentyear AND MONTH(p.Date) = @priormonth AND 3 hundred and sixty 5 days(p.Date) = @prioryear AND c.stability > p.stability

Answer 4

If the tables you are working with do not have a create datetime or an update datetime field(s) then your pretty much SOL as far as any current data is concerned.