CODEWITHSUNDEEP
sql-server
Akhil K Nambiar
Akhil K Nambiar

Reputation: 3975

Add a column to specific position in MSSQL Server

ALTER TABLE Log ADD log_id bigint IDENTITY BEFORE cust_id_fk

The above code adds a new column to last position. I want it to be added to the first position. Also I want to make it as Primary Key.

Upvotes: 14

Views: 31340

Answers (7)

ryan huh
ryan huh

Reputation: 71

According to Change Column Order in a Table, this operation is not supported using the Transact-SQL statement.

Upvotes: 1

Onga Leo-Yoda Vellem
Onga Leo-Yoda Vellem

Reputation: 296

Short answer: It's not possible.

But you may try these steps:

  1. Right click table name on object explorer
  2. Click tasks
  3. Click drop and create table
  4. Add your columns in the position you want to add them

If you have data. Copy the data and paste it on an Excel spreadsheet, edit the spreadsheet to include new columns,edit top 100 rows and paste the data back into the table.

Goodluck

Upvotes: 0

Martin Staufcik
Martin Staufcik

Reputation: 9490

Steps:

  1. Rename the original table to tablename_temp
  2. create a new table containing the new column
  3. insert into tablename select * from tablename_temp
  4. recreate foreign keys and other constraint on the new table

Upvotes: 0

Doug
Doug

Reputation: 1

In MSSMS select the table in the object explorer. Right click and select modify. That will bring a new tab where you can drag the columns into a new default order. Save and presto! Done.

Upvotes: 0

Rv3
Rv3

Reputation: 59

Even if the question is old, a more accurate about Management Studio would be required.

You can create the column manually or with Management Studio. But Management Studio will require to recreate the table and will result in a time out if you have too much data in it already, avoid unless the table is light.

To change the order of the columns you simply need to move them around in Management Studio. This should not require (Exceptions most likely exists) that Management Studio to recreate the table since it most likely change the ordination of the columns in the table definitions.

I've done it this way on numerous occasion with tables that I could not add columns with the GUI because of the data in them. Then moved the columns around with the GUI of Management Studio and simply saved them.

You will go from an assured time out to a few seconds of waiting.

Upvotes: 3

Louis
Louis

Reputation: 2890

You have to create another table and copy the data. But have a look at "ordinal position" and try to update it ?

SELECT 
   ORDINAL_POSITION
  ,COLUMN_NAME
  ,DATA_TYPE
  ,CHARACTER_MAXIMUM_LENGTH
  ,IS_NULLABLE
  ,COLUMN_DEFAULT
FROM   
  INFORMATION_SCHEMA.COLUMNS 
WHERE   
  TABLE_NAME = 'Product' 
ORDER BY 
  ORDINAL_POSITION ASC;

Primary key is another question for which you may find lots of answers.

Upvotes: -2

Ira Rainey
Ira Rainey

Reputation: 5209

You would need to drop the table and recreate it with the columns in the correct order. If you make the table changes in SSMS then it can generate a change script for you which you could then use to deploy the change to a production server.

Upvotes: 11

Related Questions