SQL with Manoj

In my [previous post] we saw how to create a new Database and a new Table.
 

Today we will see how to ADD a new column to a table and DROP an existing column from a table.

To ADD or DROP columns you have to use the ALTER TABLE statement, that is also used to change the datatype of existing columns. Please check the video and the Scripts below to check and learn about both of these actions.

Please note: while adding a new column by using ALTER TABLE ADD statement the columns are always added at the end of the table. But if you want to add the column at a specific position use the Table Designer in SSSMS. Adding column in between is not recommended as it may do data movement by introducing an intermediate temporary table. Thus it is advised to add the columns at the end only and while Querying it you can use the specific column in the middle of the SELECT statement.
 

Check video on how to ADD and DROP columns in a Table:

–> SQL Script used in the above demo:

USE [Demo]
GO

-- create a sample table:

CREATE TABLE [dbo].[Employee](
	[EmployeeID]	int				NOT NULL,
	[EmployeeName]	nvarchar(100)	NOT NULL,
	[Gender]		nchar(1)		NULL,
	[DOB]			datetime		NULL,
	[DOJ]			datetime		NULL,
	[DeptID]		int				NULL
)

-- insert some sample/test records:
INSERT INTO [dbo].[Employee]
VALUES (1, 'MANOJ PANDEY', 'M', '1990-01-01', '2010-01-01', 101)

INSERT INTO [dbo].[Employee]
VALUES (2, 'JHON K', 'M', NULL, '2010-01-01', NULL)

INSERT INTO [dbo].[Employee] ([EmployeeID], [EmployeeName])
VALUES (3, 'Brock H')
GO

SELECT * FROM [dbo].[Employee]
GO


-- Add a new COLUMN:

ALTER TABLE [dbo].[Employee]
ADD MgrID INT
GO

SELECT * FROM [dbo].[Employee]
GO


-- Add column at specific location:
BEGIN TRANSACTION
GO

CREATE TABLE dbo.Tmp_Employee
	(
	EmployeeID int NOT NULL,
	EmployeeName nvarchar(100) NOT NULL,
	Gender nchar(1) NULL,
	FathersName nvarchar(100) NULL,
	DOB datetime NULL,
	DOJ datetime NULL,
	DeptID int NULL,
	MgrID int NULL
	)  ON [PRIMARY]
GO

ALTER TABLE dbo.Tmp_Employee SET (LOCK_ESCALATION = TABLE)
GO

IF EXISTS(SELECT * FROM dbo.Employee)
	 EXEC('INSERT INTO dbo.Tmp_Employee (EmployeeID, EmployeeName, Gender, DOB, DOJ, DeptID, MgrID)
		SELECT EmployeeID, EmployeeName, Gender, DOB, DOJ, DeptID, MgrID FROM dbo.Employee WITH (HOLDLOCK TABLOCKX)')
GO

DROP TABLE dbo.Employee
GO

EXECUTE sp_rename N'dbo.Tmp_Employee', N'Employee', 'OBJECT' 
GO

COMMIT


SELECT EmployeeID, EmployeeName, Gender, MgrID, FathersName, DOB, DOJ, DeptID 
FROM [dbo].[Employee]
GO


-- Drop an existing Column:

ALTER TABLE [dbo].[Employee]
DROP Column FathersName
GO


-- Adding Column with NOT NULL values

ALTER TABLE [dbo].[Employee]
ADD Address VARCHAR(100) NOT NULL
GO
-- error
/*
Msg 4901, Level 16, State 1, Line 38
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'Address' cannot be added to non-empty table 'Employee' because it does not satisfy these conditions.
*/

ALTER TABLE [dbo].[Employee]
ADD Address VARCHAR(100) NOT NULL DEFAULT ('N/A')
GO

SELECT * FROM [dbo].[Employee]
GO

ALTER TABLE [dbo].[Employee]
DROP COLUMN Address
GO
-- error
/*
Msg 5074, Level 16, State 1, Line 55
The object 'DF__Employee__Addres__xxxxxxxx' is dependent on column 'Address'.
Msg 4922, Level 16, State 9, Line 55
ALTER TABLE DROP COLUMN Address failed because one or more objects access this column.
*/

ALTER TABLE [dbo].[Employee] 
DROP CONSTRAINT [DF__Employee__Addres__3C69FB99]
GO

ALTER TABLE [dbo].[Employee]
DROP COLUMN Address
GO


-- Final Cleanup
DROP TABLE [dbo].[Employee]
GO

Check my [next post] on how to Rename & change datatype of columns in a table.