Home > DeveloperSection > Blogs > Insert Multiple Rows into Single Table

Insert Multiple Rows into Single Table


Database Database 
Ratings:
0 Comment(s)
 2075  View(s)
Rate this:

Insert Multiple Rows into Single Table

Here we describe how to insert records into table in multiple ways. Currently when developers have to insert any value into the table they have to write multiple insert statements. First of all this is not only boring it is also very much time consuming as well. Additionally, one has to repeat the same syntax so many times that the word boring becomes an understatement.

We have demonstrated three different methods to insert multiple values into a single table.

For demonstration first we create a table.

-- SELECT DATABASE NAME

USE <DATABASE NAME>

-- CREATE TABLE

CREATE TABLE [dbo.Employee]

(

[ID] INT PRIMARY KEY IDENTITY(1,1),

[Name] VARCHAR(50)NOT NULL,

[Designation] VARCHAR(50) NOT NULL

)

Method 1: Traditional Method of INSERT…VALUE

--Traditional Method of INSERT…VALUE

INSERT INTO [dbo.Employee] VALUES ('Jems','Developer');

INSERT INTO [dbo.Employee] VALUES ('Karlosh','Designer');

INSERT INTO [dbo.Employee] VALUES ('Garry','Marketing');

INSERT INTO [dbo.Employee] VALUES ('Thomash','Helper');

INSERT INTO [dbo.Employee] VALUES ('Alvert','Recruiter');

Method 2: INSERT…SELECT

-- Method 2 - Select Union Insert

INSERT INTO [dbo.Employee] ([Name],[Designation])

SELECT 'Jems','Developer'

UNION ALL

SELECT 'Karlosh','Designer'

UNION ALL

SELECT 'Garry','Marketing'

UNION ALL

SELECT 'Thomash','Helper'

UNION ALL

SELECT 'Alvert','Recruiter'

Method 3: SQL Server 2008+ Row Construction

-- Method 3 - SQL Server 2008+ Row Construction

INSERT INTO [dbo.Employee] ([Name],[Designation])

VALUES ('Jems','Developer'), ('Karlosh','Designer'),

('Garry','Marketing'),('Thomash','Helper'),('Alvert','Recruiter')


Don't want to miss updates? Please click the below button!

Follow MindStick