Results 1 to 1 of 1
  1. #1
    Join Date
    Feb 2004
    Posts
    1

    Unanswered: Using timestamp to identify modified records.

    I have a requirement for extract modified records from an existing OLTP database table. I have always avoided using timestamp for anything other than optimistic locking. Anyone know of any reason I can/can't use it for extracting?
    Here is some sample code that I am using. Early tests suggest it twill work fine.

    Appreciate any comments.
    Thanks
    Richard.

    /*start code snippet-----------*/

    /* Table created on the same database that we are extracting from */
    -- Create table ExtractControl
    -- (ExtractID numeric (10,0) identity,
    -- DateStamp datetime default getdate(),
    -- Timestamp timestamp)

    Declare @StartExtract varbinary(8), @EndExtract varbinary(8),
    @StartDate datetime, @EndDate datetime,
    @CurrentExtractID int
    /* Insert a record to generate the current timestamp. */
    Insert into ExtractControl values ()

    /* get the previous and current timestamps */
    Select @CurrentExtractID = max(ExtractID) from ExtractControl
    Select @StartExtract = timestamp, @StartDate = DateStamp from ExtractControl where ExtractID = (@CurrentExtractID -1 )
    Select @EndExtract = timestamp, @EndDate = DateStamp from ExtractControl where ExtractID = @CurrentExtractID

    create table #IDList( ID char(10))

    /* Now select from Customers. Table is small so full table scan is ok */
    if @StartExtract < @EndExtract
    Insert #IDList
    Select CustomerID from Customer
    where timestamp between @StartExtract and @EndExtract
    else
    /* timestmap has wrapped around */
    Insert #IDList
    Select CustomerID from Customer
    where timestamp > @StartExtract
    Union
    Select CustomerID from Customer
    where timestamp < @EndExtract

    select convert(varchar(6),count(*)) + ' records updated between '
    + convert(varchar(30), @StartDate, 109) + ' and '
    + convert(varchar(30), @EndDate, 109)
    from #IDList

    /* do any processing on the #IDList table */
    -- select * from #IDList

    drop table #IDList

    /*end code snippet-----------*/
    Last edited by Richard_M; 03-03-04 at 20:37.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •