Handsome Use Of Table Type Instead Of Temprary Table In MssqlServer2005

Hello Friends Sometime you find an alternative of temprary table which also occupy spaces in our temp database.and you feel other alternative of its in some logics.
Today i will you describe you me in exposer which i used in my project for saving of data in mssqlserver into a normalize table.
Step 1: Run give below script.
USE [master]
GO
/****** Object: Database [dbPatients] Script Date: 03/26/2011 16:15:43 ******/
CREATE DATABASE [dbPatients] ON PRIMARY
( NAME = N’dbPatients’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\dbPatients.mdf’ , SIZE = 2240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’dbPatients_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\dbPatients_log.LDF’ , SIZE = 768KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @dbname=N’dbPatients’, @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY(‘IsFullTextInstalled’))
begin
EXEC [dbPatients].[dbo].[sp_fulltext_database] @action = ‘enable’
end
GO
ALTER DATABASE [dbPatients] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [dbPatients] SET ANSI_NULLS OFF
GO
ALTER DATABASE [dbPatients] SET ANSI_PADDING OFF
GO
ALTER DATABASE [dbPatients] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [dbPatients] SET ARITHABORT OFF
GO
ALTER DATABASE [dbPatients] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [dbPatients] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [dbPatients] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [dbPatients] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [dbPatients] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [dbPatients] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [dbPatients] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [dbPatients] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [dbPatients] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [dbPatients] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [dbPatients] SET ENABLE_BROKER
GO
ALTER DATABASE [dbPatients] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [dbPatients] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [dbPatients] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [dbPatients] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [dbPatients] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [dbPatients] SET READ_WRITE
GO
ALTER DATABASE [dbPatients] SET RECOVERY FULL
GO
ALTER DATABASE [dbPatients] SET MULTI_USER
GO
ALTER DATABASE [dbPatients] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [dbPatients] SET DB_CHAINING OFF
Step 2: now Make Tables with given below script.
USE [dbPatients]
GO
/****** Object: Table [dbo].[tbl_PatientDetail] Script Date: 03/26/2011 16:16:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_PatientDetail](
[intID] [int] IDENTITY(1,1) NOT NULL,
[strPatientID] [int] NULL,
[intHeight] [int] NULL,
[chrBlodGroup] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[intWeight] [int] NULL,
CONSTRAINT [PK_tbl_PatientDetail] PRIMARY KEY CLUSTERED
(
[intID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
STep 3: Make Second Table
USE [dbPatients]
GO
/****** Object: Table [dbo].[tbl_PatientMasters] Script Date: 03/26/2011 16:16:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_PatientMasters](
[intID] [int] NOT NULL,
[strFullName] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[strAddress] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[strPhoneNo] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[chrGender] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_tbl_PatientMasters] PRIMARY KEY CLUSTERED
(
[intID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
STep 4: now Run Given Below Script and view Table Type use
DECLARE @strFullName VARCHAR(40)
DECLARE @strAddress VARCHAR(200)
DECLARE @strPhoneNo VARCHAR(10)
DECLARE @chrGender CHAR(1)
DECLARE @intHeight INT
DECLARE @chrBloodGroup Char(2)
DECLARE @intWeight INT

SET @strFullName=’Lakshya’
SET @strAddress=’H.No 745,St.no 12,Patiala’
SET @strPhoneNo =’9989987156′
SET @chrGender =’M’
SET @intHeight =145
SET @chrBloodGroup =’B+’
SET @intWeight =123

DECLARE @TBLPATIENTINFO TABLE (INTID INT IDENTITY(1,1) PRIMARY KEY,STRNAME VARCHAR(40),STRADD TEXT,STRPHONE VARCHAR(10),CHRGENDER CHAR(1),INTHEIGHT INT,CHRBLOODGROUP CHAR(2),INTWEIGHT INT)
INSERT INTO @TBLPATIENTINFO VALUES(@strFullName,@strAddress,@strPhoneNo,@chrGender,@intHeight,@chrBloodGroup,@intWeight)
IF ((SELECT COUNT(*) FROM @TBLPATIENTINFO)>0)
BEGIN
INSERT tbl_PatientMasters(intID,strFullName,strAddress,strPhoneNo,chrGender)
SELECT
(
SELECT MAX(dbo.tbl_PatientMasters.intID) FROM dbo.tbl_PatientMasters
)+ INTID,STRNAME,STRADD,STRPHONE,CHRGENDER FROM @TBLPATIENTINFO
INSERT tbl_PatientDetail(strPatientID,intHeight,chrBlodGroup,intWeight)
SELECT (
SELECT MAX(dbo.tbl_PatientMasters.intID) FROM dbo.tbl_PatientMasters
),INTHEIGHT,CHRBLOODGROUP,INTWEIGHT FROM @TBLPATIENTINFO
PRINT ‘RECORD IS INSERT,DO it AGAIN GUY!!’
END
ELSE
BEGIN
PRINT ‘NO RECORD IS INSERT,TRY AGAIN GUY!!’
enjoy and learn happy programming!

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

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