SQL Basics – ADD or DROP (remove) Columns in a Table in SQL Server
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.