SQL Server 2008 New Features - Row Constructors
page 2 of 3
by Nidal Arabi
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 24462/ 68

Step by Step Explanation

Follow the easy steps below to create your environment and work area.

1.    Open Microsoft SQL Server Management Studio.

2.    Type the following listing to create your own temporary database (right click the connection node of the SQL server on the top and choose New Query Windows) and press F5.

Listing 1 - Create the temporary database

CREATE DATABASE [DbWork] ON PRIMARY 
(NAME = N'DbWork', 
FILENAME = 
N'C:\Program Files\Microsoft SQL 
Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DbWork.mdf'
, SIZE = 2048KB , FILEGROWTH = 1024KB )
 LOG ON 
(NAME = N'DbWork_log', 
FILENAME = 
N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DbWork_log.ldf'
, SIZE = 1024KB , FILEGROWTH = 10%)
Go

3.    After Creating the DBWork, right click the connection node and choose refresh in order to display the newly created database.

4.    Right Click the newly created database and choose new query.

5.    Enter the listing found below to create your customer table as indicated below and press F5.

Listing 2 - Create the customer table in the database

USE [DbWork]
GO
CREATE TABLE [dbo].[TblCustomer](
      [CustomerId] [intIDENTITY(1,1) NOT NULL,
      [CustomerName] [varchar](50) NOT NULL,
      [CustomerSince] [date] NOT NULL,
      [CustomerArea] [varchar](50) NOT NULL,
 CONSTRAINT [PK_TblCustomer] PRIMARY KEY CLUSTERED 
(
      [CustomerId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]
) ON [PRIMARY]

6.    Clear the query windows after the statement has run.

7.    Now suppose that you have to insert the data listed in table below manually.

CustomerName

CustomerSince

CustomerArea

Nidal Arabi

12/31/2007

Hamra

Kamal Diab

1/1/2008

Doha

Nisrine Salem

2/1/2008

Quebec

Hayat Salam

3/3/2008

Kuwait

Antoine Eid

4/4/2008

Morocco

8.    I am going to show you two methods to do the job (With and Without row constructors).

9.    Without a row constructor method, the listing below should be used (Press F5 to execute).

Listing 3 - Create insert statement for every record

Insert into TblCustomer (CustomerName, CustomerSince, CustomerArea)
Values ('Nidal Arabi','12/31/2007','Hamra')
 
Insert into TblCustomer (CustomerName, CustomerSince, CustomerArea)
Values ('Kamal Diab','1/1/2008','Doha')
 
Insert into TblCustomer (CustomerName, CustomerSince, CustomerArea)
Values ('Nisrine Salem','2/1/2008','Quebec')
 
Insert into TblCustomer (CustomerName, CustomerSince, CustomerArea)
Values ('Hayat Salam','3/3/2008','Kuwait')
 
Insert into TblCustomer (CustomerName, CustomerSince, CustomerArea)
Values ('Antoine Eid','4/4/2008','Morocco')

10. Now delete the content of the query window and enter the listing below to delete all records.

Listing 4 - Delete all records from the customer table

Truncate Table TblCustomer
Delete * From TblCustomer

The difference between delete and truncate is that the truncate Table statement does not log the deleted records in the SQL Server log file.

11. Now, using the row constructor feature, the listing is reduced to the following:

Listing 5 - Insert all records using the row constructor

Insert into TblCustomer (CustomerName, CustomerSince, CustomerArea)
Values      ('Nidal Arabi','12/31/2007','Hamra'),
            ('Kamal Diab','1/1/2008','Doha'),
            ('Nisrine Salem','2/1/2008','Quebec'),
            ('Hayat Salam','3/3/2008','Kuwait'),
            ('Antoine Eid','4/4/2008','Morocco')

12. Go ahead, try it in the query window and enjoy it.


View Entire Article

User Comments

Title: SQL Server 2008 New Features - Row Constructors   
Name: onedaywhen
Date: 2010-07-12 8:50:57 AM
Comment:
Delete * From TblCustomer -- That's not legal SQL syntax. This plus other evidence (table names prefixed with 'Tbl', you didn't write your SQL DDL code, etc) makes me wonder if you are more comfortable with MS Access.
Title: SQL server 2008   
Name: Vimal
Date: 2008-09-11 8:42:51 AM
Comment:
Very helpful
Title: SQL2008 - Row Constructors   
Name: Srinivas
Date: 2008-09-04 6:34:30 AM
Comment:
Great
Title: SQL2008 - Row Constructors   
Name: Mike
Date: 2008-09-03 9:35:45 PM
Comment:
That's great to know! Short, sweet, to the point.
Thanks!
Title: SQL 2000 / 2005   
Name: Richard
Date: 2008-09-02 10:30:28 AM
Comment:
For SQL 2000 / 2005:

Insert into [Table] ([Columns])
Select [Row 1 Values]
Union Select [Row 2 Values]
Union Select [Row 3 Values]
(etc.)






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-07-23 1:50:23 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search