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')
Leave Comment