SQL Loop HELP!

Question

Post reply

SQL Loop HELP!

dramaqueen

SSCommitted

Points: 1569
More actions
October 22, 2015 at 8:45 am

#301671

Hi All,
I have a problem where I want to create a loop in my script to insert 0's into my table.
I have a temp table with a list of company codes.
SELECT CODE FROM ##SENData GROUP BY CODE
This produces the following;
CODE
00C
00D
00K
00M
00Q
00T
00V
00X (there are 110 of these codes)
I want to insert data into a different table in the following format.
+------+------------+-------+---------+
| CODE | Month | Value | Measure |
+------+------------+-------+---------+
| 00C | 01/09/2015 | 0 | HAR-01 |
| 00D | 01/09/2015 | 0 | HAR-01 |
| 00K | 01/09/2015 | 0 | HAR-01 |
| 00M | 01/09/2015 | 0 | HAR-01 |
| 00Q | 01/09/2015 | 0 | HAR-01 |
| 00T | 01/09/2015 | 0 | HAR-01 |
| 00V | 01/09/2015 | 0 | HAR-01 |
| 00X | 01/09/2015 | 0 | HAR-01 |
+------+------------+-------+---------+
The month will be set from a declared variable and the others will just be hard coded.
I hope this make sense and if anyone can hold I will be really grateful.
Thanks again,
Paul

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

SELECT d.CODE, e.*

FROM (

SELECT CODE

FROM ##SENData

GROUP BY CODE

) d

CROSS JOIN (

SELECT

[Month] = GETDATE(),

Value = 0,

Measure = 'HAR-01'

) e

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ant-Green SSC Guru Points: 113509 More actions · Answer 2

If I understand right, you don't need a loop for this if the other values are static.

declare @month date ='2015-10-01'

create table #destination (code char(3), mon date, value int, measure char(6))

create table #sendata (code char(3))

insert into #sendata values

('00C'),

('00D'),

('00K'),

('00M'),

('00Q'),

('00T'),

('00V'),

('00X')

SELECT CODE FROM #SENData

insert into #destination select code, @month, 0, 'HAR-01' from #sendata group by code

select * from #destination

drop table #sendata, #destination

dramaqueen SSCommitted Points: 1569 More actions · Answer 3

Thank you so much for this response! Worked perfectly and the first time I have used a cross join 🙂