locked
SQL field default value RRS feed

  • Question

  • Hi all,

    In SQL Server 2005, I want that the field gets it's default value once the record is inserted (before saving) just the same as Access do. Is there any settings or code to make that posible with SQL?

    Thanks.

    Monday, September 27, 2010 1:34 PM

Answers

  • I don't think it's possible unless you replicate the default values manually. You can also get the default values functions, but you still will have to produce the result of the default values functions yourself manually.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Tom Li - MSFT Wednesday, September 29, 2010 7:16 AM
    • Marked as answer by Tom Li - MSFT Thursday, October 7, 2010 3:02 AM
    Monday, September 27, 2010 2:17 PM
    Answerer

All replies

  • Yes, you can set the default value in SQL Server table. Try doing this when you design the table (in Table Designer) or take a look at the ALTER TABLE command in BOL.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, September 27, 2010 1:47 PM
    Answerer
  • See the example below

    CREATE TABLE #tmp (c1 INT , dt DATETIME DEFAULT GETDATE())

    INSERT INTO #tmp (c1) VALUES (10)

    SELECT * FROM #tmp


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, September 27, 2010 1:56 PM
  • See the example below

    CREATE TABLE #tmp (c1 INT , dt DATETIME DEFAULT GETDATE())

    INSERT INTO #tmp (c1) VALUES (10)

    SELECT * FROM #tmp


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, September 27, 2010 1:56 PM
  • Sorry it seems that I did not explain it well!

    I am connecting Access XP to SQL Server threw ODBC, I am trying to insert data to the linked tables, I filled the fields default value when designing the tables (SQL Side), but when inserting a new record the fields still have a null value! untill the record is saved then it fills the requred default value, is it posible to get the default value filled imediatly on insert (and before saving the record)?

    Thanks...

    Monday, September 27, 2010 2:14 PM
  • I don't think it's possible unless you replicate the default values manually. You can also get the default values functions, but you still will have to produce the result of the default values functions yourself manually.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Tom Li - MSFT Wednesday, September 29, 2010 7:16 AM
    • Marked as answer by Tom Li - MSFT Thursday, October 7, 2010 3:02 AM
    Monday, September 27, 2010 2:17 PM
    Answerer