Create table dynamically using loop

Question

Post reply

Create table dynamically using loop

miss.delinda

SSCommitted

Points: 1616
More actions
February 22, 2010 at 9:16 pm

#139036

I create table 1 by 1 as follow,
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]
CREATE TABLE [dbo].[DERInfo_201002](
[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_201002_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_201002_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]
CREATE TABLE [dbo].[DERInfo_201003](
[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_201003_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_201003_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]
DERInfo_201001 means, DERInfo in Jan 2010.
DERInfo_201002 means, DERInfo in Feb 2010.
DERInfo_201003 means, DERInfo in Mar 2010.
Let's say, i want to create DERInfo table between Jan 2010 till Nov 2010.
I want to using loop, and my SQL program will do it dynamically. How my create table looks like?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

NewBeeSQL SSCrazy Points: 2170 More actions · Answer 1

Is this you are looking for?

declare @tblStr varchar(max)

declare @intMnth int

set @intMnth =1

while @intmnth < 12

begin

set @tblStr = 'CREATE TABLE [dbo].[DERInfo_2010'+cast(@intmnth as varchar(10))+'](

[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_2010'+cast(@intmnth as varchar(10))+'_P'+cast(@intmnth as varchar(10))+'] 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_2010'+cast(@intmnth as varchar(10))+'_P'+cast(@intmnth as varchar(10))+'] 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]'

set @intMnth = @intMnth + 1

exec(@tblStr)

end

-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

miss.delinda SSCommitted Points: 1616 More actions · Answer 2

NewBeeSQL (2/22/2010)
Is this you are looking for?
declare @tblStr varchar(max)
declare @intMnth int
set @intMnth =1
while @intmnth < 12
begin
set @tblStr = 'CREATE TABLE [dbo].[DERInfo_2010'+cast(@intmnth as varchar(10))+'](
[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_2010'+cast(@intmnth as varchar(10))+'_P'+cast(@intmnth as varchar(10))+'] 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_2010'+cast(@intmnth as varchar(10))+'_P'+cast(@intmnth as varchar(10))+'] 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]'
set @intMnth = @intMnth + 1
exec(@tblStr)
end

Yes. But i need the table name in format YYYYMM such as Jan 2010 is DERInfo_201001. currently, your code create DERInfo_20101 for Jan 2010

Prakash Shetty SSC Enthusiast Points: 113 More actions · Answer 3

Hi,

I have made a little changes in the above code

declare @tblStr varchar(max)

declare @intMnth int

declare @strMnth varchar(10)

set @intMnth =1

while @intmnth <= 12

begin

set @strMnth = case when len(cast(@intmnth as varchar(10))) = 1 then '0' + cast(@intmnth as varchar(10)) else cast(@intmnth as varchar(10)) end

set @tblStr = 'CREATE TABLE [dbo].[DERInfo_2010'+@strMnth+'](

[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_2010'+cast(@intmnth as varchar(10))+'_P'+cast(@intmnth as varchar(10))+'] 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_2010'+cast(@intmnth as varchar(10))+'_P'+cast(@intmnth as varchar(10))+'] 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]'

set @intMnth = @intMnth + 1

exec(@tblStr)

end

Hope this solves ur problem

LutzM SSC Guru Points: 107049 More actions · Answer 4

What is the specific reason for building one table per month instead of adding the related column to one table and (if required) use horizontal partitioning?

How many rows you end up with for one of your monthly tables?

How do you handle quarterly/annual reports? Using UNION ALL, I'm afraid?

I'd question the db design...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

miss.delinda SSCommitted Points: 1616 More actions · Answer 5

The tables have been segmented because of performance issue. Me going to using Partitioned Views technology.

How about that?

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 6

Im with Lutz on this one.

Not knowing your performance problem , its hard to be specific but a partitioned table would be a better solution.

Clear Sky SQL
My Blog[/url]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

I agree with the others... IF you have the Enterprise edition, then partitioned tables would probably be the way to go.

If you don't have the Enterprise edition, then the separate tables you're trying to make and correct construction of the necessary "partitioned views" can be a big help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

miss.delinda SSCommitted Points: 1616 More actions · Answer 8

miss.delinda

SSCommitted

Points: 1616

February 24, 2010 at 3:13 pm

#1124631

tq to all. all your answer is my inspiration.