I am in the middle of a data migration project, where we are taking all of the records from one database and copying them to another. The source db is MSSQL. Because I must do this during business hours, I need a way to identify which records have been updated since the last time I did an export.

I tried adding a field LAST_MODIFIED of type TIMESTAMP to my CUSTOMER tabel, thinking that I could use an SQL query like: SELECT * FROM CUSTOMER WHERE LAST_MODIFIED > '2005-09-29 14:23'. This doesn't work, because, as I found out later, the field type TIMESTAMP isn't the same as TIMESTAMP in mySQL, which is what I use mostly.

I need to which records employees have modified, so that I can export them and move them to the new system. How can I achieve this?

Thanks!

Jim

Why don't you just transfer all the records? It will overwrite ones that haven't been updated, but only with the same data...

15,000 records, all in XML format going into the new system. That method would take way too long. A single import takes the better part of a day, so I need something a bit more sophisticated then taking all records at once. Thanks though.

A single import takes the better part of a day

And only 15,000 records?

Good thing you are going to mysql :)

Anyway, to address your problem read here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ta-tz_6fn4.asp

not going to mysql... going to XML. our destination system is with an ASP, and they allow XML import only, no direct database access. Their XML parser adds much overhead to the process.

Thanks for the link, but it didn't help. Though Microsoft claims that datetime is eq to SQL92 timestamp, they are wrong. SQL92 timestamp gets updated whenever any field on the row changes, or the record itself it new. It seems that T-SQL's datetime is just that, and nothing more. If you make a change to another field on the record, the datetime field remains the same.

Microsoft sucks... Microsoft sucks... Microsoft sucks... Microsoft sucks...

Praise *nix. Praise open-source. Microsoft sucks (did I mention that already!?)

So it sounds like you need to store the "timestamp" (M$FT word for a big integer number) of the last read row. Then you can use the field in a comparison operation. Is there some way to remember the greatest "timestamp" read?

Jim,

As the previous posted alluded to, cant you just do an UPDATE on the time value on those rows that were already archived / selected / etc?

No. The reason is that the database table records are manipulated by our accounting software. I have no access to its source code. I need a way to have the timestamp automatically updated when the accounting software modifies the record. I'm doing the export to the new system in batches, as the destination system can only handle a few thousand records at a time. It's possible, and likely, that some of the records will get changed during the migration, and I need to be able to go back and ask the database which ones were in fact modified after exported, so that I can export them again to the new system.

Why is this so freaking difficult? If mySQL can handle it, then why can't MSSQL?