What are the different ways to insert data into SQL Server Table - SQL Server / TSQL Tutorial Part 100

Scenario:

As SQL Server Developer, you have to come with different ways to insert data into SQL Server Table.
Sometime you have to simply insert static records, sometime you have to insert data from another table to existing table. Let's check out different techniques to insert data into SQL Server table.

Solution:

Let's create the dbo.Customer Table first by using  below definition.

USE yourDatabaseName
Go
Create Table dbo.Customer(
Id int identity(1,1),
FName VARCHAR(50),
LName VARCHAR(50),
Age int,
DOB Date,
Salary Numeric(6,2))


1)  Use Multiple Inserts to insert data into table.


Insert into dbo.Customer(FName,LName,Age,DOB,Salary)
Values('Aamir','Shahzad',36,'1980-01-01',5000.50)
GO
Insert into dbo.Customer(FName,LName,Age,DOB,Salary)
Values ('Raza','M',33,'1983-03-03',4900.34)
GO
Insert into dbo.Customer(FName,LName,Age,DOB,Salary)
Values ('John','Smith',26,'1990-04-05',5000.50)



2) Use single Insert with multiple Values

As in above example, we use multiple inserts.Each was inserting single record. In SQL Server we can use single Insert with multiple values as shown below.

Insert into dbo.Customer(FName,LName,Age,DOB,Salary)
Values('Aamir','Shahzad',36,'1980-01-01',5000.50),
('Raza','M',33,'1983-03-03',4900.34),
('John','Smith',26,'1990-04-05',5000.50)


3)  Use Select with Insert to insert Records

We can use Insert with Select query to insert the result set returned by select query. 

Insert into dbo.Customer(FName,LName,Age,DOB,Salary)
Select 'Aamir' as FName,'Shahzad' as LName,36 as Age,'1980-01-01' as DOB,5000.50 as Salary
union all
Select 'Raza','M',33,'1983-03-03',4900.34
Union all
Select 'John','Smith',26,'1990-04-05',5000.50



4) Use Insert without provide Column Names

As you have seen in above examples, I have used column list with Insert, you don't have to use that if you know the order of columns and values you are using are also in order. I always use the column list in insert and select to make sure I am inserting correct data to table in correct columns.

--Insert without provide Column Names
Insert into dbo.Customer
Select 'Aamir' as FName,'Shahzad' as LName,36 as Age,'1980-01-01' as DOB,5000.50 as Salary
union all
Select 'Raza','M',33,'1983-03-03',4900.34
Union all
Select 'John','Smith',26,'1990-04-05',5000.50

Also we can use the insert without Column Names with Values option

Insert into dbo.Customer
Values('Aamir','Shahzad',36,'1980-01-01',5000.50),
('Raza','M',33,'1983-03-03',4900.34),
('John','Smith',26,'1990-04-05',5000.50)

5) Insert data from Another Table to Destination table

As we have seen that the select query results can be inserted into table. In above examples we have used the static values with select, You can select the data from table,view and function etc. to insert into your table. Let's say if we want to insert data into dbo.Customer table from dbo.CustomerUS table. you can use below query.

--Insert into table from another table
Select into dbo.Customer(FName,LName,Age,DOB,Salary)
Select FName,LName,Age,DOB,Salary from dbo.CustomerUS


Video Demo : How to insert data into SQL Sever Table by using T-SQL statements


3 comments: