SQL Add Column

 in SQL Server  tagged sql / add / column / attribute by

What is the SQL statement that adds a column to an existing table? The SQL statement is the ALTER TABLE statement. Below is the syntax for the ALTER TABLE statement. 

ALTER TABLE table_name
ADD column_name datatype;

Below is an example of adding a column called ProductSales to the table OrderDetails. ProductSales is set to the money data type. 

ALTER TABLE OrderDetails
ADD ProductSales money;

Let’s look at a realistic example of adding a column to a table and then updating that new column to contain information based on other columns in the table. This is like a calculated column in DAX.

Below is a SQL Server script that uses an existing database called [MikeTestTwo], which I created earlier. This script creates the table, adds some data to the table, creates a new column, and then adds some data to that new column. This has a little bit of data from Microsoft’s Northwind database. 

USE [MikeTestTwo]
GO
/****** Object:  Table [dbo].[Order Details]    Script Date: 1/8/2023 9:03:34 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OrderDetails](
	[OrderID] [int] NOT NULL,
	[ProductID] [int] NOT NULL,
	[UnitPrice] [money] NOT NULL,
	[Quantity] [smallint] NOT NULL,
	[Discount] [real] NOT NULL,
 CONSTRAINT [PK_Order_Details] PRIMARY KEY CLUSTERED 
(
	[OrderID] ASC,
	[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[OrderDetails] ([OrderID], [ProductID], [UnitPrice], [Quantity], [Discount]) VALUES (10248, 11, 14.0000, 12, 0)
INSERT [dbo].[OrderDetails] ([OrderID], [ProductID], [UnitPrice], [Quantity], [Discount]) VALUES (10248, 42, 9.8000, 10, 0)
INSERT [dbo].[OrderDetails] ([OrderID], [ProductID], [UnitPrice], [Quantity], [Discount]) VALUES (10248, 72, 34.8000, 5, 0)
INSERT [dbo].[OrderDetails] ([OrderID], [ProductID], [UnitPrice], [Quantity], [Discount]) VALUES (10249, 14, 18.6000, 9, 0)
INSERT [dbo].[OrderDetails] ([OrderID], [ProductID], [UnitPrice], [Quantity], [Discount]) VALUES (10249, 51, 42.4000, 40, 0)

GO
ALTER TABLE [dbo].[OrderDetails] ADD  CONSTRAINT [DF_Order_Details_UnitPrice]  DEFAULT ((0)) FOR [UnitPrice]
GO
ALTER TABLE [dbo].[OrderDetails] ADD  CONSTRAINT [DF_Order_Details_Quantity]  DEFAULT ((0)) FOR [Quantity]
GO
ALTER TABLE [dbo].[OrderDetails] ADD  CONSTRAINT [DF_Order_Details_Discount]  DEFAULT ((0)) FOR [Discount]
GO
ALTER TABLE OrderDetails
  ADD ProductSales money;
GO
UPDATE [OrderDetails]
  SET ProductSales = (UnitPrice * Quantity * (1 - Discount));
GO

Leave a Reply