In this article, I will explain you the different way to insert record into table in sql server. Below is 7 way to insert record into a table.

Creating a table:

I am using temporary table to explain the example. Now, create a temporary table by using below script.

create table #tblEmployee
(
Id int,
Name varchar(20),
Gender varchar(10)
)

Method 1: By using traditional way

This is simple and traditional way of inserting record. You can specify the column name.

insert into #tblEmployee values (1,'John','Male')

Method 2: By using values() constructor

insert into #tblEmployee 
values (1,'John','Male'),
         (2,'Mary','Female'),
         (3,'Rahul','Male'),
         (4,'Ram','Male')

Method 3: By using union or union all operator

insert into #tblEmployee 
select  1, 'John', 'Male' 
union all
select 2, 'Mary', 'Female'
union all
select 3, 'Rahul', 'Male'
union all
select 4, 'Ram', 'Male'

Method 4: Using dynamic query

insert into #tblEmployee
exec('
    select 1, ''John'', ''Male''
    select 2, ''Mary'', ''Female''
    select 3, ''Rahul'', ''Male''
    select 4, ''Ram'', ''Male''
   ')

Method 5: Creating a new table with specified column

select 1 Id, 'John' Name, 'Male' Gender
into #new_table
union
select 2, 'Mary', 'Female'
union
select 3, 'Rahul', 'Male'
union
select 4, 'Ram', 'Male'

Method 6: Creating a new table from existing table

Syntax: select col1,col2,....,coln into new_Table from existing_Table

select Id, Name, Gender into #tbl_temp
from #tblEmployee

Method 7: Use select within values constructor

insert into #tblEmployee 
values (1,'John','Male'),
  (2,'Mary','Female'),
  (3,'Rahul','Male'),
  ((select 4) ,'Ram',(select 'Male'))