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.

Share on FacebookTweet about this on TwitterShare on Google+Share on RedditShare on StumbleUponDigg thisFlattr the authorEmail this to someoneShare on LinkedInPrint this page

2 Responses to “Handy SQL Server Snippets”

  1. Gary Hvizdak says:

    Hey Craig!

    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,
    Gary KI4GGX
    http://www.unpcbs.com

    P.S. I don’t see any ham radio links here, are you “inactive”?

    • Craig says:

      Gary,

      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. :-)

Leave a Response