How to Add New Column in Existing View in SQL-Server 2014 Using Alter

How to add a column in TSQL after a specific column?

Unfortunately you can't.

If you really want them in that order you'll have to create a new table with the columns in that order and copy data. Or rename columns etc. There is no easy way.

Add a column with a default value to an existing table in SQL Server

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:

If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate

    a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:

The WITH VALUES is only needed when your Column is Nullable

    and you want the Default Value used for Existing Records.

If your Column is NOT NULL, then it will automatically use the Default Value

    for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:

If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.

If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),

    then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.

Special Thanks to:

    @Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.

Adding an identity to an existing column

You can't alter the existing columns for identity.

You have 2 options,

1. Create a new table with identity & drop the existing table

2. Create a new column with identity & drop the existing column

Approach 1. (New table) Here you can retain the existing data values on the newly created identity column. Note that you will lose all data if 'if not exists' is not satisfied, so make sure you put the condition on the drop as well!

CREATE TABLE dbo.Tmp_Names
    (
      Id int NOT NULL
             IDENTITY(1, 1),
      Name varchar(50) NULL
    )
ON  [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

IF EXISTS ( SELECT  *
            FROM    dbo.Names ) 
    INSERT  INTO dbo.Tmp_Names ( Id, Name )
            SELECT  Id,
                    Name
            FROM    dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go

DROP TABLE dbo.Names
go

Exec sp_rename 'Tmp_Names', 'Names'

Approach 2 (New column) You can’t retain the existing data values on the newly created identity column, The identity column will hold the sequence of number.

Alter Table Names
Add Id_new Int Identity(1, 1)
Go

Alter Table Names Drop Column ID
Go

Exec sp_rename 'Names.Id_new', 'ID', 'Column'

See the following Microsoft SQL Server Forum post for more details:

How to alter column to identity(1,1)

Add new column to view

Use CASE to generate the results as per the required condition in a new column

create view view1 as
select column1, column2,
    case when column1 = 'x' then 'test'
        when column2 != 'x' then 'a'
    end as column3
from table1;

SQL Server -- Any way to add a column and make it first column in table?

According to Microsoft you can do this only using SQL Server Management Studio.
Check this

Add a column to a table, if it does not already exist

You can use a similar construct by using the sys.columns table io sys.objects.

IF NOT EXISTS (
  SELECT * 
  FROM   sys.columns 
  WHERE  object_id = OBJECT_ID(N'[dbo].[Person]') 
         AND name = 'ColumnName'
)

Add new column with foreign key constraint in one command

As so often with SQL-related question, it depends on the DBMS. Some DBMS allow you to combine ALTER TABLE operations separated by commas. For example...

Informix syntax:

ALTER TABLE one
    ADD two_id INTEGER,
    ADD CONSTRAINT FOREIGN KEY(two_id) REFERENCES two(id);

The syntax for IBM DB2 LUW is similar, repeating the keyword ADD but (if I read the diagram correctly) not requiring a comma to separate the added items.

Microsoft SQL Server syntax:

ALTER TABLE one
    ADD two_id INTEGER,
    FOREIGN KEY(two_id) REFERENCES two(id);

Some others do not allow you to combine ALTER TABLE operations like that. Standard SQL only allows a single operation in the ALTER TABLE statement, so in Standard SQL, it has to be done in two steps.

---

Related Topics