Unanswered: How To Write A Script To Create A Table (With Unfixed Field Names)?
You may find below script stupid to you. I am trying to create a table with week number that is not fixed, i.e. March 2006 may have 4 weeks and April 5 weeks based on our company calendar.
And Field Names could be Grp, SubGrp, Week_10, Week_11, Week_12, Week_13 depending on the week being stored in TempWeekFile. We could start the month having only Week 10 and following week with Week 11 added into TempWeekFile.
My below script shows error. I have following records in my TempWeekFile
How to write a script that do the above? Has anyone done this using a better method? Please advise. Thank you.
if exists (select * from information_schema.tables where table_name='WeeklySalesToThird_Month')
drop table WeeklySalesToThird_Month
Declare @WeekCount int
Declare @Cnt int
Declare @WeekNo varchar(2)
declare @sql varchar(8000)
Select @WeekCount = count(*) from TempWeekFile
set @sql='CREATE TABLE [dbo].[WeeklySalesToThird_Work_Month]
[Grp] [varchar] (30) NULL ,
[SubGrp] [varchar] (30) NULL ,'
select @WeekNo = cast(Week_No as varchar) from TempWeekFile
set @sql=@sql+'[Week_'+@WeekNo+'] [Money] NULL,'
set @sql=@sql+' ) ON [PRIMARY]'
Last edited by mallier; 03-28-06 at 08:49.
I love cursor,though I never use it.Cos' I dont want to misuse it.
Bingo. But not for the reasons you think.
Why are you storing derived data in dedicated tables? This usually leads to trouble, and is an indication of poor application design.
You should take mallier's logic and roll it into a stored procedure instead.
If it's not practically useful, then it's practically useless.