Friday, November 07, 2014

TSQL Tutorial : Inserting Data in Tables

Use the Transact-SQL INSERT statement to add data to a table, one row at a time. Let's explore this by
adding some test data to the customers table. Enter the following SQL commands to add three rows to
customers:
INSERT INTO customers
VALUES(1,'Doe','John','123 Joshua Tree','Plano','TX','75025')
INSERT INTO customers
VALUES(2,'Doe','Jane','123 Joshua Tree','Plano','TX','75025')
INSERT INTO customers
VALUES(3,'Citizen','John','57 Riverside','Reo','CA','90120')
Now, add four rows to the orders table using the same syntax:
INSERT INTO orders
VALUES(101,'10/18/90',1,1001,123.45)
INSERT INTO orders
VALUES(102,'02/27/92',2,1002,678.90)
INSERT INTO orders
VALUES(103,'05/20/95',3,1003,86753.09)
INSERT INTO orders
VALUES(104,'11/21/97',1,1002,678.90)
Finally, insert three rows into the items table like so:
INSERT INTO items
VALUES(1001,'WIDGET A',123.45)
INSERT INTO items
VALUES(1002,'WIDGET B',678.90)
Chapter 1. Introductory Transact-SQL
5
INSERT INTO items
VALUES(1003,'WIDGET C',86753.09)
Notice that none of these INSERTs species a list of fields, only a list of values. The INSERT command
defaults to inserting a value for all columns in order, though you could have specified a column list for each
INSERT using syntax like this:
INSERT INTO items (ItemNumber, Price)
VALUES(1001,123.45)
Also note that it's unnecessary to follow the table's column order in a column list; however, the order of values
you supply must match the order of the column list. Here's an example:
INSERT INTO items (Price, ItemNumber)
VALUES(123.45, 1001)
One final note: The INTO keyword is optional in Transact-SQL. This deviates from the ANSI SQL standard
and from most other SQL dialects. The syntax below is equivalent to the previous query:
INSERT items (Price, ItemNumber)
VALUES(123.45, 1001)


TSQL Tutorial...

No comments:

Post a Comment