RSS

Multiple Inserts in one statement – Row Constructor in SQL Server 2008

01 Sep

We all know how to use Data Manipulation
Language to Insert, Delete and Update data on a SQL Server table. Sometimes we
need to insert too much data using insert statements. There are traditional and
cumbersome methods to insert huge amounts of data. Now, SQL Server 2008 provides
a new method to insert data to SQL Server tables, which simplifies the data
insertion. This article is going to illustrate different methods to insert data
to a table, including the new Row Value Constructor.

Traditionally we have three methods to insert data

Method 1

USE [master]
GO
/****** Object:  Database [MyTestDB]
Script Date: 01/09/2011 ******/
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: 01/09/2011 ******/
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 . 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 , ‘Mostafa’, ‘Elmasry’, 150000.00)
insert into MyTest1 (id ,fname ,lname , salary) values (2 , ‘Mohamed’, ‘Osman’, 250000.00)
insert into MyTest1 (id ,fname ,lname , salary) values (3 , ‘Mohamed’, ‘Omar’, 120000.00)
insert into MyTest1 (id ,fname ,lname , salary) values (4 , ‘Mahmoud’, ‘Darwish’, 151000.00)
insert into MyTest1 (id ,fname ,lname , salary) values (5 , ‘Khaled’, ‘Jamal’ , 150300.00)

Method 2

CREATE TABLE [dbo].[MyTest2](
[Id] [int] NULL,
[Fname] [varchar](100) NULL,
[Lname] [varchar](100) NULL,
[salary] [money] NULL
) ON [PRIMARY]

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

insert into MyTest1 select 1 , ‘Mostafa’, ‘Elmasry’, 150000.00
insert into MyTest1 select 2 , ‘Mohamed’, ‘Osman’, 250000.00
insert into MyTest1 select 3 , ‘Mohamed’, ‘Omar’, 120000.00
insert into MyTest1 select 4 , ‘Mahmoud’, ‘Darwish’, 151000.00
insert into MyTest1 select 5 , ‘Khaled’, ‘Jamal’ , 150300.00

Method 3

CREATE TABLE [dbo].[MyTest3](
[Id] [int] NULL,
[Fname] [varchar](100) NULL,
[Lname] [varchar](100) NULL,
[salary] [money] NULL
) GO

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

insert into MyTest3
select 1 , ‘mostafa’ , ‘Elmasry’ , 150000.00
union select 2 , ‘Mohamed’ , ‘Osman’ , 250000.00
union select 3 , ‘mohamed’ , ‘omar’ , 120000.00
union select 4 , ‘mohamed’ , ‘Ali’ , 151000.00
union select 5 , ‘mohamed’ , ‘na7la’ , 150300.00
*****—-Method 4—–******

CREATE TABLE [dbo].[MyTest4](
[Id] [int] NULL,
[Fname] [varchar](100) NULL,
[Lname] [varchar](100) NULL,
[salary] [money] NULL
)

insert into MyTest4 (id ,fname ,lname , salary) values
(1 , ‘A’ , ‘B’ , 12.00),
(2 , ‘C’ , ‘D’ , 13.00),
(3 , ‘E’ , ‘F’ , 14.00),
(4 , ‘G’ , ‘H’ , 15.00),
(5 , ‘I’ , ‘J’ , 16.00)

 
Leave a comment

Posted by on September 1, 2011 in Script

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s