Results 1 to 3 of 3
  1. #1
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    313

    How to INSERT NEW ROW into a Table with a Timestamp fields

    I have two tables in SQL Server 2008 R2. I have a regular table (tblData) with a timestamp field and a corresponding archive table (tblArchive). tblArchive is identical to tblData except it has a unique identity field (RecordID) at the beginning. Periodically I need to "archive" selected rows by inserting a new row into the archive table. I would like to use the following syntax...

    INSERT INTO tblArchive


    SELECT tblData.*
    FROM tblData
    WHERE tblData.ArchiveDate=#1/13/2013#

    However, SQL Server won't let me copy a timestamp field into a new field. It tells me I need to name all the fields to INSERT excluding the timestamp field or assign a default value to timestamp. I don't want to specify all the fields because anytime I add a new field, I have to update the SQL statement. I could not find a way to assign a default value to the timestamp field.

    Can anyone suggest a way I can structure the SELECT statement so I don't have to name all the columns explicitly?

    Thanks, Eddie

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You can't copy a timestamp field to another record. It is like an Autonumber field type in Access - the program manages it. And you cannot assign a default value - that would defeat the purpose of a timestamp field AND a default value would try and assign duplicate values.

    I don't want to specify all the fields because anytime I add a new field, I have to update the SQL statement.
    This sounds like you do not have a normalized structure if you have to keep adding fields!

  3. #3
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    313
    Thanks for your help. It's not that the data isn't normalized, it's just that we're still developing and the user is adding more bells & whistles all the time. I've already programmed all the data fields so we'll just have to live with it. Thanks again, Eddie

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Insert A TimeStamp
    By dandoescode in forum Forms
    Replies: 3
    Last Post: 06-25-2012, 12:34 PM
  2. Replies: 2
    Last Post: 12-20-2011, 07:33 AM
  3. Replies: 0
    Last Post: 03-08-2011, 05:56 PM
  4. Replies: 1
    Last Post: 12-28-2010, 11:24 AM
  5. "Internal" timestamp on table rows?
    By quahtrader in forum Access
    Replies: 1
    Last Post: 07-13-2010, 10:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums