Handy SQL Server Snippets
There are a few SQL Server tasks I do infrequently and always forget the syntax. I’m going to start collecting them here.
ALTER TABLE ExistingTable ADD NewColumn INT NOT NULL DEFAULT(0) WITH VALUES
This adds a new column with a default value of 0, and populates existing rows with 0 in the new column.
ALTER TABLE ExistingTable ADD NewColumn INT IDENTITY
This adds a new “auto increment” (identity) column and populates existing rows with values for the new column. The values are somewhat random but start at 1 and increment by 1.
INSERT INTO ExistingTable (Col1, Col2, Col3) SELECT Col1, Something AS Col2, Col3 FROM ExistingTable WHERE Conditions
This statement inserts rows into an existing table based on rows returned from the SELECT. If the SELECT clause specifies every necessary column in the same order as they appear in the CREATE statement that created the table, there’s no need to list them in the INSERT clause. In other words, it’s best to include the column names in the INSERT clause.