SQL Server with Mr. Denny

Mar 24 2008   10:00AM GMT

Back To Basics: The INSERT Statement



Posted by: Denny Cherry
Tags:
Back To Basics
INSERT statement
SQL
SQL Server 2000
SQL Server 2005
SQL Server 2008
T/SQL

While the SELECT statement is probably the most important command, the INSERT comes in handy.  The INSERT statement is used to do exactly what it sounds like, it inserts data into a table.

 There are two ways to insert data into a table.  The first is to pass in each of the values, and the second is to insert the data from a select statement.

For both commands we’ll be using a new table with this definition.
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'InsertTable')
DROP TABLE InsertTable
GO
CREATE TABLE InsertTable
(id INT,
name sysname)

First lets look at passing in the values. With this syntax we specify the names of the columns, and then specify each of the values.

INSERT INTO InsertTable
(id, name)
VALUES
(0, 'test')

Second we’ll look at the SELECT statement. There are two ways we can do this as well. The first is to load a single set of values with the select statement. When doing this you can optionally specify the column names or not.

INSERT INTO InsertTable
SELECT 0, 'test'

The second option with the SELECT statement is to use a SELECT statement from a table. All of the functionally of the SELECT statement is available when using the SELECT statement as part of the INSERT statement.

INSERT INTO InsertTable
SELECT id, name
FROM sysobjects

We can also do this with some of the more advanced functions of the SELECT statement.

INSERT INTO InsertTable
(name, id)
SELECT sysobjects.name, count(*)
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id

I hope that you find this post useful. I encourage everyone to open up Books OnLine and read through the information on the INSERT statement. It includes more examples, and some of the other options which are available to you.

Denny

1  Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • SQLWayne
    It's always good to brush up on the basics. I'm currently re-training our Cobol developers into the world of SQL Server, and I'd forgotten your second example.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: