Different methods of SQL queries to insert data in tables

Create a sample table with some data to know different methods to insert data in table with examples.

create table EmpDtl1(EmpId int,EmpName varchar(30))                

insert into EmpDtl1(EmpId,EmpName)
values( 1,'David')

insert into EmpDtl1(EmpId,EmpName)
values( 2,'Steve')

insert into EmpDtl1(EmpId,EmpName)
values( 3,'Chris')

Following are the different methods to insert data into sql table.

Insert one row at a time

insert into EmpDtl1 values(1,'one')

Insert query result set into table

create table EmpDtl2(EmpId int,EmpName varchar(30))

insert into EmpDtl2            
select * from EmpDtl1

Here condition is number of columns and respective data types returned in select query should match with insert table command.

We can specify particular columns to insert data as shown below.

insert into EmpDtl2 (EmpName)           
select EmpName from EmpDtl1

Insert query result into new table

select * into EmpCopy from EmpDtl1

* into <New Table> clause is used for copy the result set into new table that does not exist in database. In above query, it creates a new table "EmpCopy" from "EmpDtl1" table data.

Following query creates a new temporary table from the query result set.

select * into #tmpEmpCopy from EmpDtl1

Insert rows into table returned by a stored procedure

Create a stored procedure that returns data of table “EmpDtl1”.

create procedure spGetEmpDetails as
begin 
select * from EmpDtl1
end

Below query will insert data into table "EmpDtl1" returned by executing procedure.

insert into EmpDtl1            
exec spGetEmpDetails

Here, number of columns returned by stored procedure should match with the inserting table (EmpDtl1). We can also specify the columns in query to insert pirticular columns data. Note: It is not possible to insert stored procedure result set data into new table so create a table based on the result set returned by the stored procedure.