Post reply

SQL Default Values

  • April 12, 2004 at 1:55 am

    #84882

    I need to make a column have a value of 4 zero's plus a column that is already in the database.

    Example

    ACCT = 001

    I need acct1 to be = 0000001

    I would like this to happen every time a row is inserted.

    Thanks for any help

    Alexzander N. Nepomnjashiy

    Technical editor for Wrox Press:
    "SQL Server 2000 Fast Answers"

    Technical editor for Apress:
    "Honeypot for Windows"
    "SQL Server Yukon Revealed"

  • April 13, 2004 at 1:23 am

    #502501

    You should use a trigger for this.

    Default values only accept constants.

  • April 13, 2004 at 1:33 am

    #502503

    Thank you

    Alexzander N. Nepomnjashiy

    Technical editor for Wrox Press:
    "SQL Server 2000 Fast Answers"

    Technical editor for Apress:
    "Honeypot for Windows"
    "SQL Server Yukon Revealed"

  • April 13, 2004 at 1:39 am

    #502504

    If column ACCT1 needs to change when ACCT changes, consider concatenating the 4 zeros in your application instead of storing it in a table. (after fetching the rows).

    Saves Disk space and Network I/O on the server.

  • April 13, 2004 at 7:04 am

    #502534

    Try also a computed column.  Eg.

    create table xxx

    (acct char(3),

    acct1 as '0000' + acct)

    insert xxx values ('001')

    insert xxx values ('002')

    insert xxx values ('003')

    select * from xxx

    acct acct1  

    ---- -------

    001  0000001

    002  0000002

    003  0000003

    (3 row(s) affected)

     

    update xxx set acct = replace(acct, '0', '1')

    select * from xxx

    acct acct1  

    ---- -------

    111  0000111

    112  0000112

    113  0000113

    (3 row(s) affected)

     

     


    Cheers,
    - Mark

  • April 13, 2004 at 7:10 am

    #502539

    Mark, thank you VERY MUCH!

    Alexzander N. Nepomnjashiy

    Technical editor for Wrox Press:
    "SQL Server 2000 Fast Answers"

    Technical editor for Apress:
    "Honeypot for Windows"
    "SQL Server Yukon Revealed"

  • April 13, 2004 at 9:03 am

    #502582

    OK, now alexnep knows how to do it, but SeekQuel had the correct answer albeit for the wrong reasons.  Adding the column by whatever means is still a clear violation of 1NF and should never be done.

  • April 13, 2004 at 10:31 am

    #502605

    I knew I'm a natural talent without knowing it !

Viewing 8 posts - 1 through 7 (of 7 total)

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