Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 6, 2008

Row Value Constructor in SQL Server 2008

By Muthusamy Anantha Kumar aka The MAK

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. 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.

» See All Articles by Columnist MAK



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date