'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.

sql-server


Solution 1:[1]

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.

Solution 2:[2]

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.

Solution 3:[3]

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

Solution 4:[4]

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

Solution 5:[5]

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

Solution 6:[6]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Rv3
Solution 2 Doug
Solution 3 Martin Staufcik
Solution 4 Onga Leo-Yoda Vellem
Solution 5 KyleMit
Solution 6 Louis

Related Questions