Skip to main content
Database Administration & Troubleshooting

ALTER TABLE ADD Multiple Columns: Syntax & Troubleshooting

5 min read

alter table add multiple columns is the SQL Server T-SQL statement that adds two or more new columns to an existing table in a single atomic operation, reducing schema change overhead.

Syntax

ALTER TABLE { schema_name.}table_name
ADD 
    column1_name data_type [ NULL | NOT NULL ] [ DEFAULT constant_expression ] [ constraint ],
    column2_name data_type [ NULL | NOT NULL ] [ DEFAULT constant_expression ] [ constraint ],
    ...;

Tested on SQL Server 2022 with default transaction isolation level.

Options and Flags

Clause Type Default Description
NULL / NOT NULL nullable constraint NULL Controls whether the column can store NULL values.
DEFAULT value expression N/A Specifies a default value inserted when no value is provided; can use WITH VALUES for existing rows.
WITH VALUES keyword N/A Assigns the default to all existing rows for a new column; only valid with DEFAULT.
IDENTITY property N/A Makes column an auto-incrementing integer; required after data type.
ROWGUIDCOL property N/A Marks a uniqueidentifier column as the row GUID column.
SPARSE property N/A Optimizes storage for columns with many NULL values.
See also  SQL Server DATEADD Function: Syntax, Parameters, and Examples

Usage Examples

Add two non-nullable columns with defaults

ALTER TABLE dbo.Orders
ADD 
    CreatedDate datetime NOT NULL DEFAULT GETDATE(),
    IsActive bit NOT NULL DEFAULT 1;

Adds CreatedDate and IsActive columns to the Orders table. Existing rows receive the default value GETDATE() and 1 only if WITH VALUES is appended. Without WITH VALUES, defaults apply only to new inserts.

Add nullable columns plus a computed column

ALTER TABLE dbo.Employees
ADD
    MiddleName nvarchar(50) NULL,
    HireYear AS YEAR(HireDate) PERSISTED;

Adds a nullable string column and a persisted computed column HireYear derived from the existing HireDate column. Persisted columns consume disk space but join efficiently.

Add multiple columns with an identity column

ALTER TABLE dbo.AuditLog
ADD
    LogId bigint IDENTITY(1,1) NOT NULL,
    EventType varchar(50) NOT NULL DEFAULT 'INFO',
    EventTime datetime2 NOT NULL DEFAULT SYSDATETIME();

Adds an auto-incrementing identity column LogId (though note: a table can have only one identity column) plus two more columns. This operation is not allowed if the table already has an identity column.

Troubleshooting & Common Errors

Error Message/Code Root Cause Resolution Command
Msg 102: Incorrect syntax near ‘(‘ Using MySQL/Oracle parentheses style in SQL Server. 
ALTER TABLE t ADD c1 int, c2 varchar(10);  -- no parentheses
Msg 2714: Duplicate column name Column already exists in the table. 
-- Drop existing column first if desired
ALTER TABLE t DROP COLUMN c1;
ALTER TABLE t ADD c1 int, c2 varchar(10);
Msg 4902: Cannot find the object ‘…’ Table does not exist or is in a different schema. 
-- Verify with
SELECT * FROM sys.tables WHERE name = 'tablename';
-- Use fully qualified name: dbo.tablename
Long transaction log growth Adding columns with defaults on large tables logs column values for all rows. 
-- Split into batches or use online operations
-- Alternatively, add nullable column first, then update, then add NOT NULL constraint

Cross-Database Comparison

Database Syntax Notes
SQL Server ALTER TABLE t ADD c1 int, c2 varchar(10); No parentheses around column list; supports WITH VALUES for defaults on existing data.
MySQL ALTER TABLE t ADD (c1 int, c2 varchar(10)); Parentheses required; FIRST or AFTER keyword positions columns.
PostgreSQL ALTER TABLE t ADD COLUMN c1 int, ADD COLUMN c2 varchar(10); Each column needs its own ADD COLUMN; PostgreSQL does not support single-set ADD (col1, col2).
Oracle ALTER TABLE t ADD (c1 int, c2 varchar2(10)); Parentheses required; each new column separated by comma.
See also  Concatenation In SQL Query — Syntax & Examples

Frequently Asked Questions

What is the difference between comma-separated ADD clauses and multiple ALTER TABLE statements for adding columns?

Answer: Comma-separated ADD clauses execute as one atomic transaction; multiple statements require per-statement commits and risk intermediate state if a later statement fails.

For example, in MySQL and PostgreSQL, single ALTER TABLE t ADD COLUMN c1 INT, ADD COLUMN c2 INT; commits all columns together. Multiple statements each acquire separate table locks and can leave partial schema changes.

When should I use the COLUMN keyword in each ADD clause when adding multiple columns?

Answer: Use COLUMN only for readability; it is optional in MySQL and PostgreSQL but required in Oracle when mixing data types.

In MySQL, ALTER TABLE t ADD COLUMN c1 INT, ADD c2 TEXT; is valid (mixing works). In Oracle, you must specify COLUMN for every new column if any column includes a default or constraint. Omitting it in Oracle causes syntax error: ALTER TABLE t ADD (c1 INT, c2 TEXT);

How do I fix ERROR 1060 (42S21): Duplicate column name when adding multiple columns?

Answer: Remove duplicate column names from the ADD list or verify existing columns with DESCRIBE table before executing.

The error occurs when any column name in the ADD clause already exists in the table. To avoid it, check current schema: SHOW COLUMNS FROM table_name; Then ensure no name collision. If you must proceed anyway, use IF NOT EXISTS where supported (PostgreSQL, MySQL 8.0+): ALTER TABLE t ADD COLUMN IF NOT EXISTS c1 INT; Note: IF NOT EXISTS only works per single column, not in multi-add.

See also  Delete Row SQL: Syntax, Clauses, Examples & Troubleshooting

Does comma-separated ADD COLUMNS syntax work on Amazon RDS for PostgreSQL?

Answer: Yes, Amazon RDS for PostgreSQL fully supports standard ALTER TABLE.

RDS PostgreSQL runs engine version 9.6+ with same DDL behavior as community PostgreSQL. Example used in production: ALTER TABLE orders ADD COLUMN region_id INT, ADD COLUMN priority VARCHAR(10) DEFAULT 'normal'; For Aurora PostgreSQL, syntax is identical. No cloud-specific flags are needed.

What is the fastest way to add multiple columns to a large table on MySQL with minimal downtime?

Answer: Use a single ALTER TABLE with multiple ADD COLUMN clauses, and enable ALGORITHM=INSTANT (MySQL 8.0+).

This method performs metadata-only changes, reducing lock time. Example: ALTER TABLE huge_table ADD COLUMN c1 INT, ADD COLUMN c2 VARCHAR(100), ALGORITHM=INSTANT; For older MySQL or when adding columns mid-table (not at end), use ALGORITHM=INPLACE (LOCK=NONE) to allow concurrent DML. Always test on a replica first.