Insert data for range of ids - 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

General SQL Server Forums

New to SQL Server Programming

Insert data for range of ids

Author

Topic

mavericky
Posting Yak Master

117 Posts

Posted - 2011-11-09 : 17:32:42

Hi,
I have a table which has following columns:
ID AssetTypeID Hour UtilizationFactor
1 1 1 .2
2 1 2 .3
3 1 3 .4
4 1 4 .5
ID is the PK..it keeps incrementing.
I have data in this format. ie. For every AssetTypeID, I have to enter values for 4 hours and utilization factor for each hour.
AssetTypeID range is 1 to 25.
Is there any way to insert data for 25 different AssetTypeIDs by sql code and not manually?

Thanks,
Mavericky

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-11-09 : 17:46:30

maybe something like this - this assumes you want the Hours and UtilizationFactors repeating for each AssetTypeID:


select number + 1 as ID
       ,(number / 4) + 1 as AssetTypeID
       ,(number % 4) + 1 as Hour
       ,((number % 4) + 2)/10.0 as UtilizationFactor
from   master..spt_values
where  type = 'P'
and    number between 0 and 99

EDIT:

here's the output:
ID          AssetTypeID Hour        UtilizationFactor
----------- ----------- ----------- ---------------------------------------
1           1           1           0.200000
2           1           2           0.300000
3           1           3           0.400000
4           1           4           0.500000
5           2           1           0.200000
6           2           2           0.300000
7           2           3           0.400000
8           2           4           0.500000
9           3           1           0.200000
10          3           2           0.300000

....

95          24          3           0.400000
96          24          4           0.500000
97          25          1           0.200000
98          25          2           0.300000
99          25          3           0.400000
100         25          4           0.500000

(100 row(s) affected)

Be One with the Optimizer
TG

mavericky
Posting Yak Master

117 Posts

Posted - 2011-11-09 : 20:47:35

Thanks TG but can anybody give example using cte?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-11-10 : 10:02:49

Why?
This gives the same result:


with cte (ID,AssetTypeID,[hour],UtilizationFactor)
as
(
       select 1,1,1,.2
       union all       
       select [id] + 1
              ,case when ([id]-1)/4 = id/4 then AssetTypeID else Assettypeid + 1 end
              ,case when ([id]-1)/4 = id/4 then [hour] + 1 else 1 end
              ,case hour when 1 then .3 when 2 then .4 when 3 then .5 when 4 then .2 end
       from   cte
       where  id < 100
)
select * from cte

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources