Saturday, June 20, 2009

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.







No comments: