locked
add column to table RRS feed

  • Question

  • hi

    i create a table with two field called ID and Name

    i enter value into it

    now i want add a Field Email to it type of varChar not null

    please help me how to it do

    Thursday, February 16, 2012 12:03 PM

Answers

  • Try

    CREATE TABLE MYTABLE(ID INT,NAME VARCHAR(20))
    INSERT INTO MYTABLE VALUES(1,'MYNAME')
    ALTER TABLE MYTABLE ADD  EMAIL VARCHAR(30) DEFAULT 'MYID@EMAIL.COM' NOT NULL
    
    SELECT * FROM MYTABLE


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, February 16, 2012 12:37 PM
  • I can think of 2 approaches. Which is more appropriate will depend on your environment.

    1st approach:

     Create the column as nullable

    ALTER TABLE t ADD email varchar(50) NULL


    Populate the column

    INSERT INTO t.email select email from <source_of_email_addresses>

    Make the column not null

    ALTER TABLE t ALTER COLUMN email varchar(50) NOT NULL

    2nd approach:

    Create a new table, wit ID, Name, and Email not null.

    Insert data into new table from old table and source of email addresses

    Drop old table

    REname new table to name of old table.


    (Twitter | Blog)

    • Marked as answer by vahidbakhtiary Thursday, February 16, 2012 5:31 PM
    Thursday, February 16, 2012 1:27 PM

All replies

  • Try

    CREATE TABLE MYTABLE(ID INT,NAME VARCHAR(20))
    INSERT INTO MYTABLE VALUES(1,'MYNAME')
    ALTER TABLE MYTABLE ADD  EMAIL VARCHAR(30) DEFAULT 'MYID@EMAIL.COM' NOT NULL
    
    SELECT * FROM MYTABLE


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, February 16, 2012 12:37 PM
  • You can alter the table with the script Manish provided although adding not null email column with default value does not make a lot of sense. You would end up with bunch of the rows in the table with not null and invalid value. So you need to put some logic to the app to handle it.

    As the side note I would suggest to a rebuild the clustered index after alteration. New not null column increases the size of the row so you most likely end up with page splits and fragmentation.



    Thank you!

    My blog: http://aboutsqlserver.com

    Thursday, February 16, 2012 1:17 PM
  • I can think of 2 approaches. Which is more appropriate will depend on your environment.

    1st approach:

     Create the column as nullable

    ALTER TABLE t ADD email varchar(50) NULL


    Populate the column

    INSERT INTO t.email select email from <source_of_email_addresses>

    Make the column not null

    ALTER TABLE t ALTER COLUMN email varchar(50) NOT NULL

    2nd approach:

    Create a new table, wit ID, Name, and Email not null.

    Insert data into new table from old table and source of email addresses

    Drop old table

    REname new table to name of old table.


    (Twitter | Blog)

    • Marked as answer by vahidbakhtiary Thursday, February 16, 2012 5:31 PM
    Thursday, February 16, 2012 1:27 PM
  • Considering there is already data in the TABLE, your INSERT should be an UPDATE.
    Thursday, February 16, 2012 1:42 PM
    Answerer
  • thanks from all

    your code is right but in my table i have many record for ID and Name

    for example :

    ID          Name

    1             divi

    2             jenifer

    3             alen

    4             dani

    5             rihana

    .               .

    .               .

    1000    alex

    and i want create field Email with a Default value for count records

    please help me

    Thursday, February 16, 2012 4:48 PM
  • To ADD Email with a default, see Manish's reply above.
    Thursday, February 16, 2012 5:00 PM
    Answerer
  • thanks for your answer

    if posible show me with code

    thanks

    Thursday, February 16, 2012 5:04 PM
  • The code is in Manish's reply above:

    CREATE TABLE MYTABLE(ID INT,NAME VARCHAR(20))
    INSERT INTO MYTABLE VALUES(1,'MYNAME')
    ALTER TABLE MYTABLE ADD  EMAIL VARCHAR(30) DEFAULT 'MYID@EMAIL.COM' NOT NULL
    
    SELECT * FROM MYTABLE

    Is there something wrong with the example?

    Thursday, February 16, 2012 5:18 PM
    Answerer
  • thanks from all for help

    Thursday, February 16, 2012 5:30 PM