Writing Transact-SQL (Part 2)

Insert data in a Table

Basic Syntax is : INSERT <table name> (<column list>) Values (<value list>)

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')
GO

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')
GO

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.')
GO

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)
GO