Add new column in sql table

Question

I want to insert new column in existing table with 2nd position.

Now i have columns order like

Emp_id, Emp_Name, Address, phoneNo.

I want to add "Gender" in near Emp_Name.

Emp_id, Emp_Name, Gender, Address, phoneNo). 

I can't delete this table and create new table.

Answer 1

You can not do this programmatically without creating a new table.

if you are allowed to create and delete tables then: create a new table:

CREATE TABLE new_table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
...
);

move the data from your main table to the new one then delete the old table.

DROP TABLE my_table

after all rename the new table to the name of which was on your old deleted table.

--alternative: you can reorder columns in your host application if possible!

Answer 2

Hi you may try the following:

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_TableName
    (
    Emp_id INT NOT NULL,
    Emp_Name VARCHAR(100) NULL,
    Gender BIT NULL,
    [Address] VARCHAR(100) NULL,
    phoneNo int NULL
    )  ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Tmp_TableName)
     EXEC('INSERT INTO dbo.Tmp_TableName (Emp_id, Emp_Name, [Address],phoneNo)
        SELECT Emp_id, Emp_Name, [Address],phoneNo FROM dbo.TableName WITH     (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.TableName
GO
EXECUTE sp_rename N'dbo.Tmp_TableName', N'TableName', 'OBJECT' 
GO
COMMIT

Make sure to change TableName to your actual table name.

Giannis

Answer 3

You can do it but what for?

SQLFiddle demo

Alter table T ADD Gender varchar(10);

Alter table T ADD Address2 varchar(100),phoneNo2 varchar(100);
update T set Address2=Address,PhoneNo2=PhoneNo;
Alter table T DROP COLUMN Address,PhoneNo;

Alter table T ADD Address varchar(100),phoneNo varchar(100);
update T set Address=Address2,PhoneNo=PhoneNo2;
Alter table T DROP COLUMN Address2,PhoneNo2;