Need help with script - 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

Transact-SQL (2000)

Need help with script

Author

Topic

tocroi72
Yak Posting Veteran

89 Posts

Posted - 2004-09-20 : 10:23:03

Hi all

I have this script:

Declare @Var int, @sql nvarchar(2000),@column nvarchar(100)
Set @var = 1
While @Var <= 32
begin
set @column = 'day' + convert(varchar,@var)

SET @sql = 'INSERT INTO Master SELECT YandMColumn,' + convert(varchar,@var) + ',' + @Column + ' From High'
Execute @sql
set @var = @var + 1
--print @sql
End

WHen i run the script, it returns me this erro message below
Could not find stored procedure 'INSERT INTO Master SELECT YearColumn,1,day1 From High'.

I have a table of data for a year like this
YandMColumn|Day1|day2|day3.....Day31|
1996/01 12 32 43
1996/02 14 31 56

and I want to create a table look like this

YandMColumn|Day|Amount
1996/01 1 12
1996/01 2 32
1996/01 3 43
1996/02 1 14
1996/02 2 31
1996/02 3 56

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-20 : 10:47:51

first try executing it like:

Exec(@sql)
instead of
Execute @sql

But beyond that, it seems like you may not be approaching this right... you may want to do subqueries or something more set-based.

ie:


Insert Into master
Select
YandMColumn,
Day1 = (Select count(*) From High Where day(someDate)=1),
Day2 = (Select count(*) From High Where day(someDate)=2),
Day3 = (Select count(*) From High Where day(someDate)=3),
...
From (Select distinct YandMColumn From High) A

Corey