Insert record

  • Hi All,

    I have table with Identity column with seed of 1. And i need to find the records inserted for a last one week which are inserted into the table how do we find this ?

    Table structure

    columnname datatype

    ID int IDENTITY column(1,1)

    DateModifieddatetime

    LegacyID nvarchar

    Category_IDint

    Title_ID int

    NameGiven nvarchar

    NameMiddle nvarchar

    NameFamily nvarchar

    But i can't use the date modified column as this will get updated if there is a modifications to table record.

    Please suggest.

    Thanks,

    Gangadhar

  • Gangadhar,

    Whenever you design a table always keep two columns named created_date and modified_date. Whenever a record is inserted into a table then created_date and modified_date will be getdate(). However when a record gets modified then only the modified_date column will be updated.

    Satnam

  • given your current table structure, you can't reliably find records inserted for any time period.

  • As others have already pointed out, your current table design won't allow you to reliably query only those rows inserted as of a specific date. For what it's worth, just to get you out of this jam, the following query will return those rows that were inserted past a specific date. Also, the 2nd condition, the one with the subquery on max(id), will probably weed out those rows that were inserted before the given date and then modified afterward. This method assumes the identity column is always being inserted sequentially, which it most probably is unless you've been using "set identity_on on". Going forward, you really need to add a column containing the date inserted.

    select * from MyTable

    where DateModified >= '2011-01-23'

    and id >

    (

    select max(id)

    from MyTable

    where DateModified < '2011-01-23'

    );

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Viewing 4 posts - 1 through 3 (of 3 total)

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