Dynamic create table - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Dynamic create table

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-06-05 : 16:31:40

Hi, every one,

I could use a big help here. Currently, I have following t-sql that creates a #table. Notice, each month has two columns in it (mthX & fmthX). See below:

CREATE TABLE #SalesHist
( mth1 VARCHAR(12) , fmth1 VARCHAR(12) , mth2 VARCHAR(12) , fmth2 VARCHAR(12) ,
mth3 VARCHAR(12) , fmth3 VARCHAR(12) , mth4 VARCHAR(12) , fmth4 VARCHAR(12) ,
mth5 VARCHAR(12) , fmth5 VARCHAR(12) , mth6 VARCHAR(12) , fmth6 VARCHAR(12) ,
mth7 VARCHAR(12) , fmth7 VARCHAR(12) , mth8 VARCHAR(12) , fmth8 VARCHAR(12) ,
mth9 VARCHAR(12) , fmth9 VARCHAR(12) , mth10 VARCHAR(12) , fmth10 VARCHAR(12) ,
mth11 VARCHAR(12) , fmth11 VARCHAR(12) , mth12 VARCHAR(12) , fmth12 VARCHAR(12))

Now the new requirement asked to create one column (mthX) per month, EXCEPT for "current" month, which should still has mthX & fmthX, i.e. TWO columns in the table.

The business logic dictates what "Current" Month is.
For instance, if @intMonth = 5, then next is the right script.

CREATE TABLE #SalesHist
( mth1 VARCHAR(12) mth2 VARCHAR(12) ,
mth3 VARCHAR(12) , mth4 VARCHAR(12) ,
mth5 VARCHAR(12) , fmth5 VARCHAR(12) ,
... )

I want to create a dynamic t-sql, but couldn't get it work the way I want.

Thanks!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-05 : 16:45:38

You can create the table and add the column dynamically. Something like this:


CREATE TABLE #SalesHist 
( mth1 VARCHAR(12) , mth2 VARCHAR(12) ,
mth3 VARCHAR(12) ,  mth4 VARCHAR(12) , 
mth5 VARCHAR(12) , mth6 VARCHAR(12) , 
mth7 VARCHAR(12) , mth8 VARCHAR(12) , 
mth9 VARCHAR(12) ,  mth10 VARCHAR(12) ,
mth11 VARCHAR(12) ,  mth12 VARCHAR(12) ) 


Declare @initmonth INT, @sql nVARCHAR(100)
Set @initmonth = 2
SET @sql = 'ALTER TABLE #SalesHist ADD fmth'+ Convert(Varchar,@initmonth) + ' Varchar(12)'
EXEC sp_ExecuteSQL @sql

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-06-05 : 17:07:31

Great idea!

I totally forgot the order of any given column in the table is unrelavent, since I am going to spell out each of them anyway!

I do have a little more difficulty after that, which is in my Insert.

I need to have this dynamically.
INSERT INTO #SalesHist (mth1, mth2, mth5, fmth5, mth6, mth7...)
SELECT mth1, ...mth5, fmth5, fmth6, fmth7...)

In another word, after the current month my select will take the fmthX column instead, and for current mth, it should take both.

Thanks!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-05 : 17:32:59

Can you post the source information? Does the souce table have 24 columns (2 for each month)?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-06-05 : 17:43:29

Yes, the source table dose have two columns for each month. Any month earlier than current month has historical data in mth, and fmth is null, while any month in the future only has forcast data in fmth and mth is null. Onl current mont has both mth and fmth loaded with data.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-05 : 18:43:23

So the issue is how to change the SELECT accordingly..right?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

ranganath
Posting Yak Master

209 Posts

Posted - 2007-06-06 : 02:39:29

Drop Table #SalesHist

CREATE TABLE #SalesHist
( mth1 VARCHAR(12) , mth2 VARCHAR(12) ,
mth3 VARCHAR(12) , mth4 VARCHAR(12) ,
mth5 VARCHAR(12) , mth6 VARCHAR(12) ,
mth7 VARCHAR(12) , mth8 VARCHAR(12) ,
mth9 VARCHAR(12) , mth10 VARCHAR(12) ,
mth11 VARCHAR(12) , mth12 VARCHAR(12) )

Declare @sql NVarchar(1000), @i varchar(100)
Set @i = 1
while (@i < = 12)
Begin
Set @sql = ' '
Select @sql = 'Alter Table #SalesHist Add Fmth' + Convert(Varchar,@i) + ' varchar(12)'
Set @i = @i + 1
EXEC sp_ExecuteSQL @sql
End

Select * From #SalesHist

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-06 : 10:19:27

Not sure why you want denormalised structure
Refer these
http://www.datamodel.org/NormalizationRules.html
http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources