13 May 2009 @ 10:59AM

Updated: 25 Jan 2010 @ 01:06PM
Click the New Query button to open a query window. To the left in the toolbar, verify that the drop down has your new database listed in it. In the screenshot below, I have it selected to clankiller. We're going to start with a basic SQL insert. This is just covering the basics of SQL... I don't plan on going into it deeply.


Within the query window I have my actual insert statement. The insert statement begins with the words INSERT INTO. I have the words capitalized but that's not actually necessary. However, I find it much easier to read SQL statements when the commands are capitalized.

Next I have the name of the table I'm inserting into, in this case the users table. Then I open parenthesis, list all the columns I'm inserting data into, then close the parenthesis. Note I have the columns expanded on the left. This makes it easy to remember what columns I have in that table, their data type and what their names are. I don't have userid in the list because it's the identity and you can't insert into an identity. Why not? It's autonumber... it fills itself in.

Next is the word VALUES. This specifies that the next bit will have the values I'm filling into the columns. I then open parenthesis and write out my values. The values go in the same order as the columns I specified above. In this example, Satis gets inserted into username (and then again into firstName), 1 gets inserted into enabled, etc.

You may notice that some fields are surrounded by single quotes (') and others are not. Basically, any type of textual or character data needs to be surrounded with quotes. 'Satis' is text. Meanwhile, numerical data is not surrounded by quotes because it is not textual. As an example, '1' is not the same as 1. With quotes, '1' is a string that happens to contain 1. On the other hand, 1 is the number 1. If you try to stick a string into a numerical field, or a number into a string field (without surrounding it in quotes), you'll get an error. Additionally, strings without quotes mean column names in SQL, so if you forget to surround a string with quotes you may get an error or unexpected consequences.

You may also notice getdate(). This is a function in sql. When you write getdate() it inserts the current date and time, as stamped from the sql server. Remember that... getdate() is the time of the sql server at the time the insert is executed. So if it's in a different time zone, it won't show your local time, it will show the sql server's local time.

Finally we hit the Execute button to run the insert statement. In the messages section, I get the message (1 row(s) affected). That lets you know your sql statement succeeded and how many rows were affected. With a simple insert like this, you would expect only one row to ever be affected. More complicated statements can affect more rows. If you'd made an error in your sql syntax, rather than the rows affected you'd have an error in this section.

Next we take a look at a basic SELECT statement.
Comments (0)