Want to create table in looping

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Want to create table in looping

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-02-22 : 23:01:59

Let's say, below is my table


CREATE TABLE [dbo].[DERInfo_201001](
	[trnxid] [int] IDENTITY(1,1) NOT NULL,	
	[DDate] [datetime] NOT NULL,
	[TID] [varchar](20) NOT NULL,
	[RID] [varchar](20) NOT NULL,
	[TripN] [varchar](10) NOT NULL,
	[Busn] [varchar](10) NOT NULL,	
	[dtGrp] [datetime] NOT NULL,
 CONSTRAINT [PK_DERInfo_201001_P01] PRIMARY KEY CLUSTERED 
(
	[TID] ASC,
	[dtGrp] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [DERInfo_201001_P01] UNIQUE NONCLUSTERED 
(
	[TID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Above table named DERInfo_201001.

201001 means, table in Jan 2010.

I intent, to create 4 table between Jan 2010 to April 2010
How my SQL to create table looks like? So, i just pass dtFrom (DateFrom) and dtTo (DateTo), then my SQL will create the table dynamically.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-02-22 : 23:18:44

--I am not going in validations (from date should be less than to date)

Create Procedure GenDynTable (@FromDate datetime, @ToDate Datetime)
As
Begin

Declare @StartingMon as int , @EndingMon as int, @SqlStmt as Varchar(8000)

Set @StartingMon = Cast(year(@FromDate)as varchar) + Left(Convert(varchar,@FromDate,101),2)
Set @EndingMon = Cast(year(@ToDate) as varchar) + Left(Convert(varchar,@ToDate,101),2)

While @StartingMon <= @EndingMon
Begin

Set @SqlStmt ='CREATE TABLE [dbo].[DERInfo_' + Cast(@StartingMon as varchar) +'](
[trnxid] [int] IDENTITY(1,1) NOT NULL,
[DDate] [datetime] NOT NULL,
[TID] [varchar](20) NOT NULL,
[RID] [varchar](20) NOT NULL,
[TripN] [varchar](10) NOT NULL,
[Busn] [varchar](10) NOT NULL,
[dtGrp] [datetime] NOT NULL,
CONSTRAINT [PK_DERInfo_' + Cast(@StartingMon as varchar) +'_P01] PRIMARY KEY CLUSTERED
(
[TID] ASC,
[dtGrp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [DERInfo_' + Cast(@StartingMon as varchar) +'_P01] UNIQUE NONCLUSTERED
(
[TID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]'
Exec (@SqlStmt)
Set @StartingMon = @StartingMon + 1

End
End

Also check:
http://www.sommarskog.se/dynamic_sql.html

Regards,
Bohra

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-02-23 : 06:05:04

tq sir.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-02-23 : 07:22:49

quote:
Originally posted by Delinda

tq sir.

Welcome

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-23 : 10:20:35

Why create dynamic tables? this is hardly ever a good idea.....

What do you *actually* want to accomplish.

A partitioned view might be your friend.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources