MS SQL Insert Statement

Today, I want to talk a little about the MS SQL Insert statement. It seems there are many people asking exactly how to write an insert statement while others are interested in learning more about the performance risks associated with inserts.

There are two basic styles for the MS SQL Insert statment:

INSERT INTO Table_Name
(
Column 1,
Column n
)
SELECT Column 1,
Column n
FROM
WHERE

For example:

DECLARE @CurrentMonth datetime
SET @CurrentMonth = DatePart(month, GetDate())

INSERT INTO PetsOnSale
(
Name,
PetType,
Age,
Price
)
SELECT Name,
PetType,
Age,
(Price * 0.90) AS SalePrice
FROM AllPets
WHERE MonthPetOnSale = @CurrentMonth

You will use the above MS SQL Insert statement style when you want to populate a table with data pulled from one or more other tables.


The other style of the insert statement is:

INSERT INTO
(
Column 1,
Column n
)
VALUES
(
Column 1,
Column n
)

For example:

INSERT INTO PetsOnSale
(
Name,
PetType,
Age,
Price
)
VALUES
(
'Fido' AS Name,
'Dog - Beagle' AS PetType,
18 AS Age, -- assume we're storing age as months not years
0 AS SalePrice -- Fido is free to a good home!
)

In this case we are inserting a single record and we are hard-coding the values for the data. Maybe we got a new pet beagle and the previous owner says they just want to give Fido away free to a good home. So we did a special manual insert statement to take care of this.

More likely you will use this style of MS SQL Insert statement when performing an insert statement that is receiving a list of values from a web page.

For example, our pet shop may have a website that has a special admin panel that allows them to enter all of their pets information into a web form and this information is then sent to the database.

Generally, you'd create a procedure (AddPet) that will be called from the webpage and receive the values that were entered into the web form.

In this case, you will have several parameters that will hold the information and the insert statement inside the AddPet procedure would look something like this:

INSERT INTO PetsOnSale
(
Name,
PetType,
Age,
Price
)
VALUES
(
@Name, -- Fido in this case
@PetType, -- Dog - Beagle
@Age, -- 18 months old was entered on the web page
@SalePrice -- FREE! was selected on the web form causing a 0 to be submitted for the price.
)

That wraps up this post. I hope this helps you to better understand how to use the MS SQL Insert statement.

My SELECT statement has hung!

One of the first roadblocks many developers run into when using SQL Server for the first time is firing off a query that simply selects data, and having the query never return a result. After spending several minutes of their precious life-force watching nothing happen, they get aggravated, cancel the query, and try again, and again, and again. The query just sits there and does nothing but mock the query writer. This is especially frustrating when the table only contains a couple hundred rows and you know that one of the undeniable constants of the universe is that SQL Server should return that result set immediately, but here it is 4:02 later, 4:03 later, 4:04 later and still no results. Report writers also often run into this problem.

The cold hard truth is that you probably haven’t done anything wrong. It’s a shocking realization, but here we go: SQL Server will place locks on data even when you are simply SELECTing data!

OK, now that you have fallen out of your chair, whimpered, and returned to your former place, let’s dive into this. There is actually a logical reason. Basically, SQL Server does stuff super fast, and it has to make sure that multiple users aren’t trying to read and update the same data at the same time. This ensures that what you see is really what is on the disk. These locks that are put on the data when you select are known as read locks. These locks keep other processes from coming along and changing the data that is in your result set before your process finishes selecting the data. Imagine if you got back a result set with half the records having been updated by another process, and the other half not updated by that process. If you work with financial data, particularly highly volatile data (think stocks) this would be very, very, very bad. SQL Server takes the conservative road and ensures this won’t happen.

If your not running a highly volatile system and don’t need to worry about some records being updating during your select, then I have a simple answer for you. Actually, I have two:
1) Check out Books Online for “SET TRANSACTION ISOLATION LEVEL” and considering setting this value to the “READ UNCOMMITTED” setting. After issueing that command, your queries will no longer worry about running into locks they find on other records, and they will come back to you, or
2) In your FROM clause, add the “WITH (NOLOCK) hint. This will also tell the server to ignore if a record has a lock on it, but to simply take whatever value happens to be there. For example: SELECT * FROM CUSTOMER_DATA WITH (NOLOCK)

For a little further reading, try searching for “SQL Server” and “Dirty Reads.” A dirty read is the short-hand name for ignoring locks and simply reading the data from disk, whatever it may be.

Speak up!

So the holidays have come and gone, and that old familiar “back to work” phrase is making it’s rounds in my head. It’s time to pump up the ‘ol Blog with some new topics.

Here’s the deal…

We have written several articles now, so you hopefully have an idea of the type of content we can provide. We’d like to start a feedback loop going here. It would be most helpful to hear from you about the topics you’d like to see on this blog. As well, all other comments are welcome to any of the posts we provide. In other words, WE WANNA HEAR FROM YOU!

So, please post comment to any of our posts, or if you prefer a quieter approach, send an e-mail. The address is (please forgive our obscuring the address, but we really aren’t into spam): sqlcoach(then that little squiggly at sign thingy that you see in all e-mail addresses)gmail.com.

Thanks in advance to all those who help us out with your comments!