Menu
How to add a column to a SQL Server table with a default value that is equal to value of an existing column?
I tried this T-SQL statement:
ALTER TABLE tablename ADD newcolumn type NOT NULL DEFAULT (oldcolumn) but it's giving an error:
The name "oldcolumn" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
318
To add a column at a specific position within a table row, use FIRST or AFTER col_name . The default is to add the column last. You can also use FIRST and AFTER in CHANGE or MODIFY operations to reorder columns within a table.
Step 1: Create a new column with alter command. ALTER TABLE table_name ADD column_name datatype; Step 2: Insert data in a new column.
If you know SQL then you probably know that you can add columns to an existing table in SQL Server using the ALTER command. It not only allows you to add a column but to drop columns as well. You can also add or drop constraints using the ALTER command.
Try this:
ALTER TABLE tablename ADD newcolumn type NOT NULL DEFAULT (0) Go Update tablename SET newcolumn = oldcolumn Where newcolumn = 0 Go The AFTER INSERT trigger approach involves overhead due to the extra UPDATE statement. I suggest using an INSTEAD OF INSERT trigger, as follows:
CREATE TRIGGER tablename_on_insert ON tablename INSTEAD OF INSERT AS INSERT INTO tablename (oldcolumn, newcolumn) SELECT oldcolumn, ISNULL(newcolumn, oldcolumn) FROM inserted This does not work though if the oldcolumn is an auto-identity column.
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
