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.
2 thoughts on “Handy SQL Server Snippets”
I was looking for some of your neat K2/K3 assembly photos to show to a professional photographer who will be doing some work for Ken and I. Are those photos still online somewhere?
Changing subjects, I see that you know SQL. Can you suggest a good starting point tutorial for someone who knows PHP but doesn’t know the first thing about SQL?
TNX & 73,
P.S. I don’t see any ham radio links here, are you “inactive”?
Sorry for the delay. I get mostly comment spam here on the blog so I don’t always see the real comments. Looks like I haven’t been here for a while to check. Sorry.
I’ll send you an email regarding the K2 and K3 photos. The short story is that the site they were on gradually became inaccessible for reasons I couldn’t figure out. I moved my blog but haven’t re-uploaded those photos.
It’s been a long time (15 years) since I was a SQL newbie. I learned by having to maintain someone else’s code. I bought a little SQL pocket guide to help with syntax. Other than that, I’ve done a lot of Google searches and learned by making mistakes. 🙂