Saturday, April 12, 2014

T SQL Tutorial : INSERT


T SQL Tutorial: INSERT Statement

In this T SQL Tutorial you will learn INSERT statement with syntax, examples, and practice exercises. There are 2 syntaxes for the INSERT statement depending on whether you are inserting one record or multiple records.

Description
The TSQL INSERT statement is used to insert a one or more records into a table.

Syntax
The syntax for the TSQL INSERT statement when inserting a single record using the VALUES keyword is:

INSERT INTO table
(column1, column2, ...)
VALUES
(expression1, expression2, ... );

For inserting multiple records using this insert statement you need to write multiple time insert statement. N number of insertion n number of statements.

Or the syntax for the T SQL INSERT statement when inserting multiple records using a SELECT statement is:

INSERT INTO table
(column1, column2, ... )
SELECT expression1, expression2, ...
FROM source_tables
WHERE conditions;

table can be used to insert the records into a table and also can use filter conditions on source table.
TSQL Tutorial : Segmentation of above statement

column1, column2 are the columns in the table to insert values.
expression1, expression2 are the values to assign to the columns in the table. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on.

source_tables is the source table when inserting data from another table.

Conditions are conditions that must be met for the records to be inserted.
Note

·         When inserting records into a table using the TSQL INSERT statement, you must provide a value for every NOT NULL column.

·         You can omit a column from the TSQL INSERT statement if the column allows NULL values.

Example - Using VALUES keyword

Let's look at an example showing how to use the TSQL INSERT statement. The simplest way use the INSERT statement is to insert one record into a table using the VALUES keyword.

For example:

INSERT INTO Customer
(customer_id, customer_name)
VALUES
(222, 'Rohit');

This INSERT statement example would insert one record into the customer table. This new record would have a customer_id of 222 and a customer_name of rohit.

Example - Using SELECT statement

You can also create more complicated TSQL INSERT statements using SELECT statement.
For example:

INSERT INTO customer
(customer_id, customer_name)
SELECT client_id, name
FROM cleint
WHERE region = 'north america';

By placing a SELECT statement within the INSERT statement, you can perform multiples inserts quickly.

No comments:

Post a Comment