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.