How to add a column at a specific position in a table?

sql-server-2008sql-server-2008-r2

Possible Duplicate:
SQL Server 2008 : Cannot Insert new column in the middle position and change data type

Hi i am using SQL SERVER 2008 & 2008 R2 i would like to add a column in a table at a specific position using alter command. is there any way keyword using which we can add a column at a specific position ? thanks! in adavanced.

Best Answer

Not very easy in sql server..

I think the best way is to add the column at the end of the table and create a view with desired order of columns..

another option..

put all the data in a temp table and recreate the actual table with correct column order, then insert the data back to the actual table from temp table

Related Solutions

Sql-server – How to check if a column exists in a SQL Server table

SQL Server 2005 onwards:

IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'columnName'
          AND Object_ID = Object_ID(N'schemaName.tableName'))
BEGIN
    -- Column Exists
END

Martin Smith's version is shorter:

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
    -- Column Exists
END

Sql – How to get column names from a table in SQL Server

You can obtain this information and much, much more by querying the Information Schema views.

This sample query:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'

Can be made over all these DB objects:

Best Answer

Related Solutions

Sql-server – How to check if a column exists in a SQL Server table

Sql – How to get column names from a table in SQL Server

Related Topic