Insert problem - 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)

Insert problem

Author

Topic

kickone
Starting Member

1 Post

Posted - 2002-09-24 : 05:11:13

ID MONTH TRAF
---------------
1 1 150
1 2 10
1 3 0
2 1 10
2 2 10
3 1 0

I want to create a stored procedure which will create for each <ID> 12 month, without duplicates.

I want something like this.

ID MONTH TRAF
---------------
1 1 150
1 2 10
1 3 0
1 4 0
1 5 0
1 6 0
1 7 0
1 8 0
1 9 0
1 10 0
1 11 0
1 12 0
2 1 10
2 2 10
2 3 0
2 4 0
2 5 0
2 6 0
2 7 0
2 8 0
2 9 0
2 10 0
2 11 0
2 12 0
3 1 10
3 2 10
3 3 0
3 4 0
3 5 0
3 6 0
3 7 0
3 8 0
3 9 0
3 10 0
3 11 0
3 12 0

I was tryed in different ways, but i get duplicates for the existent months i don't know how to insert only the months which are not in table.

My code was:

insert into traf (id, month. traf)

select id,1,0
from trafic
union
select id,2,0
from trafic
union
select id,3,0
from trafic
union
select id,4,0
from trafic
union
select id,5,0
from trafic
union
select id,6,0
from trafic
union
select id,7,0
from trafic
union
select id,8,0
from trafic
union
select id,9,0
from trafic
union
select id,10,0
from trafic
union
select id,11,0
from trafic
union
select id,12,0
from trafic
union

Can somebody help me with some ideas or some code samples?

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-24 : 05:48:12

see http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=20092 to get a sequence of numbers

then something like

select b.ID, Month = seq, coalesce(tbl.Traf, 0)
from (seq = select 1 union select 2 union ... union select 12) as a
join (select distinct ID from tbl) as b
left outer join tbl on tbl.month = a.seq and tbl.ID = b.ID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources