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
insert into dbo.Customer(FName,LName,Age,DOB,Salary)
ReplyDeleteSelect FName,LName,Age,DOB,Salary from dbo.CustomerUS
good job!
ReplyDeleteNICE!
ReplyDelete