Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - "INSERT" and "UPDATE" Statements - Inserting and Updating Data In Tables

By: FYIcenter.com

(Continued from previous topic...)

How to insert and update data into a table with "INSERT" and "UPDATE" statements?

This is the third tutorial of a quick lesson on creating database objects with Transact-SQL statements. This lesson shows you how to create a database, create a table in the database, and then access and change the data in the table. Because this lesson is an introduction to using Transact-SQL, it does not use or describe the many options that are available for these statements. This tutorial assumes that you are running SQL Server Management Studio Express.

Now that you have created the Products table, you are ready to insert data into the table by using the INSERT statement. After the data is inserted, you will change the content of a row by using an UPDATE statement. You will use the WHERE clause of the UPDATE statement to restrict the update to a single row. The four statements will enter the following data.

1Clamp12.48Workbench clamp
50Screwdriver3.17Flat head
75Tire Bar Tool for changing tires
30003mm Bracket.52

The basic syntax is: INSERT, table name, column list, VALUES, and then a list of the values to be inserted. The two hyphens in front of a line indicate that the line is a comment and the text will be ignored by the compiler. In this case, the comment describes a permissible variation of the syntax.

To insert data into a table - Execute the following statement to insert a row into the Products table that was created in the previous task. This is the basic syntax.

-- Standard syntax
INSERT dbo.Products (ProductID, ProductName, Price, ProductDescription)
    VALUES (1, 'Clamp', 12.48, 'Workbench clamp')

The following statement shows how you can change the order in which the parameters are provided by switching the placement of the ProductID and ProductName in both the field list (in parentheses) and in the values list.

-- Changing the order of the columns
INSERT dbo.Products (ProductName, ProductID, Price, ProductDescription)
    VALUES ('Screwdriver', 50, 3.17, 'Flat head')

The following statement demonstrates that the names of the columns are optional, as long as the values are listed in the correct order. This syntax is common but is not recommended because it might be harder for others to understand your code. NULL is specified for the Price column because the price for this product is not yet known.

-- Skipping the column list, but keeping the values in order
INSERT dbo.Products
    VALUES (75, 'Tire Bar', NULL, 'Tool for changing tires.')

The schema name is optional as long as you are accessing and changing a table in your default schema. Because the ProductDescription column allows null values and no value is being provided, the ProductDescription column name and value can be dropped from the statement completely.

-- Dropping the optional dbo and dropping the ProductDescription column
INSERT Products (ProductID, ProductName, Price)
    VALUES (3000, '3mm Bracket', .52)

To update the products table - Type and execute the following UPDATE statement to change the ProductName of the second product from Screwdriver, to Flat Head Screwdriver.

UPDATE dbo.Products
    SET ProductName = 'Flat Head Screwdriver'
    WHERE ProductID = 50

(Continued on next topic...)

  1. How to use Transact-SQL statements to access the database engine?
  2. How to create new databases with "CREATE DATABASE" statements?
  3. How to create new table with "CREATE TABLE" statements?
  4. How to insert and update data into a table with "INSERT" and "UPDATE" statements?
  5. How to read data in a table with "SELECT" statements?
  6. How to create a login to access the database engine using "CREATE LOGIN" statements?
  7. How to create a user to access a database using "CREATE USER" statements?
  8. How to create a view and a stored procedure using "CREATE VIEW/PROCEDURE" statements?
  9. How to grant a permission using "GRANT EXECUTE" statements?
  10. How to delete database objects with "DROP" statements?

Related Articles:


Other Tutorials/FAQs:


Related Resources:


Selected Jobs: