You are here

Insert Statement

insert statement

INSERT STATEMENT

In this post we will discuss about Insert statement in SQL server 2016.

  • The insert statement is used for inserting one or more rows into table.
  • SQL support a number of different methods that you can use for inserting data into table. Those are like INSERT VALUES, INSERT SELECT, INSERT EXEC, SELECT INTO.

INSERT VALUES:

  • The insert values statement is used to insert one or more rows in to table based on columns.
  • Using this statement we can insert more then one rows at the same time into table.
  • Specifying the columns names is optional if there are same number of values as there are columns and the values are sequenced in exactly according to the columns.
  • This statement does not specify a values for a column with an identity property because this property generates values for the column automatically.

Syntax:

Insert into <table_name>(col1,col2,…) values(exp1,exp2…),(exp1,exp2);

Example:

Create table tbl_a

(

                                    id int NULL,

                                    name nchar(10) NULL,

                                    city nchar(10) NULL

)

Insert into tbl_a(id, name, city) values(1,’Riya’,’vvnagar’),(2,’Anika’,’Anand’);

Select * from tbl_a;

Output:

id name city
1 Riya vvnagar
2 Anika Anand

INSERT SELECT:

  • The insert select statement insert the result set returned by a query into the specified table.
  • Here specifying the column name is also optional.

Syntax:

Insert into <table_name>(col1,col2..) select statement;

Example:

Create table tbl_b

(

id int NULL,

name nchar(10) NULL,

city nchar(10) NULL

)

Insert into tbl_b(id, name, city) select * from tbl_a;

Select * from tbl_b;

output:

Id name city
1 Riya vvnagar
2 Anika Anand

Explanation:

In above example we create a table tbl_b and using insert select statement we Insert the table tbl_a records into tbl_b.

INSERT EXEC:

  • Using the insert exec statement we can insert the result return by the stored procedure in to the specified target table.
  • For using insert exec statement first of all create the stored procedure.

Syntax:

Create procedure proce_name

<paramenter>

As

Select query;

  • After creating the stored procedure write insert exec statement .

Syntax:

Insert into <table_name>(col1,col2,…) exec storedprocedure_name;

Example:

Create table tbl_c

(

                                    id int NULL,

                                    name nchar(10) NULL,

                                    city nchar(10) NULL

)

Create proc insertdata     –create procedure

@city as nchar

As

           Select * from tbl_a where city=@city;

Insert into tbl_c(id, name, city) exec insertdata @city=’Anand’;

Select * from tbl_c;

Output:

id name City
2 Anika Anand

Select into:

  • The select into creates a new table from the result of a query.
  • Select into involves a query(select part) and a target table(the into part).it creates a target table based on the definition of the source and insert result from the query into table.

Syntax: 

Select <col1,col2>

Into <target_table>

From <source_table>;

  • The benefit of select into statement is that when the database’s recovery model is not set to all, but instead to either simple or bulk logged, the statement uses an optimized logging mode.
  • This statement also has a drawback is that you have only limited conrol over the definition of the target table.

Example:

Select id, name, city

Into tbl_d

From tbl_a where city=’vvnagar’;

Select * from tbl_d;

Output:

id name city
1 Riya vvnagar

Keep Visiting TechwithR.com for more upcoming top technical articles.

Detail course about DBA visit TechNet Consultancy

You can also by SQL 70-761 book write by me from Amazon.
You can also by Kotlin Book write by me from Amazon.

Leave a Reply

Top