Alter Table Add Column Syntax

SqlSql ServerTsqlDdl

Sql Problem Overview

I'm trying to programmatically add an identity column to a table Employees. Not sure what I'm doing wrong with my syntax.

ALTER TABLE Employees
  ADD COLUMN EmployeeID int NOT NULL IDENTITY (1, 1)

ALTER TABLE Employees ADD CONSTRAINT
	PK_Employees PRIMARY KEY CLUSTERED 
	(
	  EmployeeID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
	ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

What am I doing wrong? I tried to export the script, but SQL Mgmt Studio does a whole Temp Table rename thing.

UPDATE: I think it is choking on the first statement with "Incorrect syntax near the keyword 'COLUMN'."

Sql Solutions

Solution 1 - Sql

Just remove COLUMN from ADD COLUMN

ALTER TABLE Employees
  ADD EmployeeID numeric NOT NULL IDENTITY (1, 1)

ALTER TABLE Employees ADD CONSTRAINT
        PK_Employees PRIMARY KEY CLUSTERED 
        (
          EmployeeID
        ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Solution 2 - Sql

This is how Adding new column to Table

ALTER TABLE [tableName]
ADD ColumnName Datatype

E.g

ALTER TABLE [Emp]
ADD Sr_No Int

And If you want to make it auto incremented

ALTER TABLE [Emp]
ADD Sr_No Int IDENTITY(1,1) NOT NULL

Solution 3 - Sql

The correct syntax for adding column into table is:

ALTER TABLE table_name
  ADD column_name column-definition;

In your case it will be:

ALTER TABLE Employees
  ADD EmployeeID int NOT NULL IDENTITY (1, 1)

To add multiple columns use brackets:

ALTER TABLE table_name
  ADD (column_1 column-definition,
       column_2 column-definition,
       ...
       column_n column_definition);

COLUMN keyword in SQL SERVER is used only for altering:

ALTER TABLE table_name
  ALTER COLUMN column_name column_type;

Solution 4 - Sql

It could be doing the temp table renaming if you are trying to add a column to the beginning of the table (as this is easier than altering the order). Also, if there is data in the Employees table, it has to do insert select * so it can calculate the EmployeeID.

Categories
Recommended Sql Solutions
Recommended Sql Server Solutions
Recommended Tsql Solutions
Recommended Ddl Solutions

Previous Solution:

Best way to obfuscate an e-mail address on a website?

JavascriptHtmlEmailObfuscation

Next Solution:

What is ModelState.IsValid valid for in ASP.NET MVC in NerdDinner?

asp.net MvcValidationNerddinner

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionBuddyJoeView Question on Stackoverflow
Solution 1 - SqlVikramView Answer on Stackoverflow
Solution 2 - SqlChiragkumar ThakarView Answer on Stackoverflow
Solution 3 - SqlDzianis YafimauView Answer on Stackoverflow
Solution 4 - Sqlneouser99View Answer on Stackoverflow