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] [int] IDENTITY(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 = ON) ON [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.