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.