Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2004
    Posts
    32

    Unanswered: Row insert timestamp?

    Just a quick question: Does SQL server 2000 store any identity information for tables in terms of the time a row gets inserted or are you forced to track this yourself? Thanks!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Track it yourself

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(
    	  Col1 int IDENTITY(1,1) PRIMARY KEY
    	, Col2 Char(1)
    	, Col3 datetime DEFAULT getdate()
    )
    GO
    
    INSERT INTO myTable99(Col2)
    SELECT 'A' UNION ALL
    SELECT 'B' UNION ALL
    SELECT 'C'
    GO
    
    SELECT * FROM myTable99
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2004
    Posts
    32
    But but! I'm lazy.... Can't microsoft just do it for me!? Oh well guess I'll just have to add a trigger or something. Thanks!

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by blm14_cu
    But but! I'm lazy.... Can't microsoft just do it for me!? Oh well guess I'll just have to add a trigger or something. Thanks!
    Did you try my code?

    And lazy is good....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Here, Here's lazy...

    uncomment the EXEC to exeute the statements...

    But

    BE CAREFUL

    You can't easily get rid of a column once you add it.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ooops..forgot the code

    Code:
    
    Use Northwind
    GO
    
    DECLARE @SQL varchar(8000)
    
    DECLARE myCursor99 CURSOR
    FOR
    SELECT 'ALTER TABLE ['+TABLE_NAME
    	+'] ADD myDateCol datetime DEFAULT GETDATE() NOT NULL'
      FROM INFORMATION_SCHEMA.TABLES
    
    OPEN myCursor99
    
    FETCH NEXT INTO @SQL
    
    WHILE @@FETCH_STATUS = 0
      BEGIN
    	SELECT @SQL
    --	EXEC(@SQL)
    	FETCH NEXT INTO @SQL
      END
    
    CLOSE myCursor99
    DEALLOCATE myCursor99
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Can't easily get rid of a column?

    Brett's solution is the simplest if you just want to record the insert date on a record. If you need to monitor updates then you will have to write a trigger. I use the CHECKSUM functions verify dirty data.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by blindman
    Can't easily get rid of a column?

    Brett's solution is the simplest if you just want to record the insert date on a record. If you need to monitor updates then you will have to write a trigger. I use the CHECKSUM functions verify dirty data.
    Yo blind dude....

    Want to post how you get rid of a column?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I use ALTER TABLE DROP COLUMN myself. You can do it in Enterprise Mangler too.

    -PatP

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Being one of the Lazy DBAs, I do it directly from Enterprise Mangler or have EM generate a script that I can edit.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What a scrub...there's my DB2 Roots showing..

    Thanks guys

    Code:
    SELECT 'ALTER TABLE ['+TABLE_NAME+'] DROP CONSTRAINT ['+TABLE_NAME+'_myDateCol_Default]'
      FROM INFORMATION_SCHEMA.TABLES
    GO
    
    SELECT 'ALTER TABLE ['+TABLE_NAME+'] DROP COLUMN [myDateCol]'
      FROM INFORMATION_SCHEMA.TABLES
    GO
    Oh and the original code is wrong

    Code:
    Use Northwind
    GO
    
    SET NOCOUNT ON
    
    DECLARE @SQL varchar(8000)
    
    DECLARE myCursor99 CURSOR
    FOR
    SELECT 'ALTER TABLE ['+TABLE_NAME
    	+'] ADD myDateCol datetime CONSTRAINT '
    	+'['+TABLE_NAME+'_myDateCol_Default] DEFAULT GETDATE() NOT NULL'
      FROM INFORMATION_SCHEMA.TABLES
    
    OPEN myCursor99
    
    FETCH NEXT  FROM myCursor99 INTO @SQL
    
    WHILE @@FETCH_STATUS = 0
      BEGIN
    	SELECT @SQL
    --	EXEC(@SQL)
    	FETCH NEXT  FROM myCursor99 INTO @SQL
      END
    
    CLOSE myCursor99
    DEALLOCATE myCursor99
    
    SET NOCOUNT OFF
    sheesh...what a scrub.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oh dear, Brett...

    I guess, on the bright side, your work just got a lot easier.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Funny thing is, I've never had a need to drop a column.....

    Anyone?

    Usually it's part of massive reengineering effort....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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