Traditionally we have three methods to insert data. They are as shown below.

Method 1

Let us assume that we have a database MyTestDB and we have a table MyTest1 as shown below.

USE [master]
GO
/****** Object:  Database [MyTestDB]    
  Script Date: 04/12/2008 00:46:37 ******/
IF  EXISTS (SELECT name FROM sys.databases 
  WHERE name = N'MyTestDB')
DROP DATABASE [MyTestDB]
GO
Create database MyTestDB
Go
Use [MyTestDB]
Go
IF  EXISTS (SELECT * FROM sys.objects 
  WHERE object_id = OBJECT_ID(N'[dbo].[MyTest1]') 
  AND type in (N'U'))
DROP TABLE [dbo].[MyTest1]
GO
USE [MyTestDB]
GO
/****** Object:  Table [dbo].[MyTest1]    
  Script Date: 04/12/2008 00:48:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MyTest1](
 [Id] [int] NULL,
 [Fname] [varchar](100) NULL,
 [Lname] [varchar](100) NULL,
 [salary] [money] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Now let’s add 5 rows of data using a traditional ANSI insert SQL statement as shown below. Here we are going to use the INSERT SQL Statement with a VALUE clause to insert data.

insert into MyTest1 (id ,fname ,lname , salary) values (1 , 'John' , 'Smith' , 150000.00)
insert into MyTest1 (id ,fname ,lname , salary) values (2 , 'Hillary' , 'Swank' , 250000.00)
insert into MyTest1 (id ,fname ,lname , salary) values (3 , 'Elisa' , 'Smith' , 120000.00)
insert into MyTest1 (id ,fname ,lname , salary) values (4 , 'Liz' , 'Carleno' , 151000.00)
insert into MyTest1 (id ,fname ,lname , salary) values (5 , 'Tony' , 'Mcnamara' , 150300.00)

Result

(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)

Method 2

Let’s assume that we have a table MyTest2 on the above database MyTestDB as shown below.

USE [MyTestDB]
GO
/****** Object:  Table [dbo].[MyTest2]    Script Date: 04/12/2008 00:48:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
/****** Object:  Table [dbo].[MyTest2]    Script Date: 04/12/2008 00:48:04 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTest2]') AND type in (N'U'))
DROP TABLE [dbo].[MyTest2]
GO
CREATE TABLE [dbo].[MyTest2](
 [Id] [int] NULL,
 [Fname] [varchar](100) NULL,
 [Lname] [varchar](100) NULL,
 [salary] [money] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Now let’s add 5 rows of data using a traditional insert SQL statement as shown below. Here we are going to use the INSERT SQL Statement with a SELECT clause to insert data.

insert into MyTest2 select 1 , 'John' , 'Smith' , 150000.00
insert into MyTest2 select 2 , 'Hillary' , 'Swank' , 250000.00
insert into MyTest2 select 3 , 'Elisa' , 'Smith' , 120000.00
insert into MyTest2 select 4 , 'Liz' , 'Carleno' , 151000.00
insert into MyTest2 select 5 , 'Tony' , 'Mcnamara' , 150300.00

Result

(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)

Method 3

Let’s assume that we have a table MyTest3 on the above database MyTestDB as shown below.

USE [MyTestDB]
GO
/****** Object:  Table [dbo].[MyTest3]    Script Date: 04/12/2008 00:48:04 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTest3]') AND type in (N'U'))
DROP TABLE [dbo].[MyTest3]
GO
USE [MyTestDB]
GO
/****** Object:  Table [dbo].[MyTest3]    Script Date: 04/12/2008 00:48:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MyTest3](
 [Id] [int] NULL,
 [Fname] [varchar](100) NULL,
 [Lname] [varchar](100) NULL,
 [salary] [money] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Now let’s add 5 rows of data using a traditional insert SQL statement as shown below. Here we are going to use the INSERT SQL Statement with a SELECT and UNION clause to insert data.

insert into MyTest3 
select 1 , 'John' , 'Smith' , 150000.00
union select 2 , 'Hillary' , 'Swank' , 250000.00
union select 3 , 'Elisa' , 'Smith' , 120000.00
union select 4 , 'Liz' , 'Carleno' , 151000.00
union select 5 , 'Tony' , 'Mcnamara' , 150300.00

Result:

(5 row(s) affected)

Method 4

Let’s assume that we have a table MyTest4 on the above database MyTestDB as shown below.

USE [MyTestDB]
GO
/****** Object:  Table [dbo].[MyTest4]    Script Date: 04/12/2008 00:48:04 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTest4]') AND type in (N'U'))
DROP TABLE [dbo].[MyTest4]
GO
USE [MyTestDB]
GO
/****** Object:  Table [dbo].[MyTest4]    Script Date: 04/12/2008 00:48:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MyTest4](
 [Id] [int] NULL,
 [Fname] [varchar](100) NULL,
 [Lname] [varchar](100) NULL,
 [salary] [money] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Now let’s add 5 rows of data using the new Row Value Constructor insert SQL statement as shown below. Here we are going to use the INSERT SQL Statement with a Row Value Constructor.

insert into MyTest4 (id ,fname ,lname , salary) values 
(1 , 'John' , 'Smith' , 150000.00),
(2 , 'Hillary' , 'Swank' , 250000.00),
(3 , 'Elisa' , 'Smith' , 120000.00),
(4 , 'Liz' , 'Carleno' , 151000.00),
(5 , 'Tony' , 'Mcnamara' , 150300.00)

Result:

(5 row(s) affected)

Conclusion

This article illustrated different methods to insert data to a table, including the new Row Value Constructor that simplifies the bulk data insertion.