Looping and Cross Joins with Cursors... 1

[MsHart2u]

Here is my dilemma... I have inherited a DTS package that has the following script in it over 50 times.

<SCRIPT BEGIN >[blue]
INSERT INTO tbl_sold
(import_date, isbn, week_num, area, units)
SELECT vw_current_week.Import_Date,
tbl_records_temp.isbn, vw_current_week.week_num, 'area68' AS area, tbl_records_temp.area68
FROM tbl_records_temp
CROSS JOIN vw_current_week
WHERE (tbl_records_temp.area68 > 0)
[/blue]<SCRIPT END>

The data is in a temp table that is over 50 columns wide (it comes to us this way)... We've decided to change the structure and add more fields. I need to move each column(area) to its own record with a record id as well as look up an id for the isbn alternatively and use today as the import_date... (whew!!) There is obviously an easier way this could have been done the the above. That is where you can advise me... please.

So the current headers are currently like this:
isbn, week_num, area00, area01,area02

Rather than refer to the isbn I want to look up its id I need
id, import_date, isbn.id, week_num, units, area

So I need to loop through the column names to break up the data into individual records. I was thinking of using cursors in a stored procedure, but I've never used a cross join in one. Any advise you have is greatly appreciated.

I've done only one stored procedure, prior to this and I am a little intimidated. We are talking millions of records, multiple weeks times +50 columns.

Thanks in advance.

Kathy
When you dig another out of their troubles, you find a place to bury your own.—Anonymous
The person who says it can't be done shouldn't interrupt the person who is doing it.—Chinese Proverb

 

[TLeaders]

Kathy

I did not build the data to test this but it should be close to what you want.

good luck
Tom

declare @ssql char (2000)
declare @cnt int
declare @cntA char (2)
set @cnt = 1

while @cnt < 69
 begin
  set @cntA = (REPLICATE('0', 2 - DATALENGTH(ltrim(@cnt))) + ltrim(@cnt))
  set @ssql = '
  INSERT INTO tbl_sold (import_date, isbn, week_num, area, units)
  SELECT     
  vw_current_week.Import_Date, 
  tbl_records_temp.isbn,
  vw_current_week.week_num, 
  ''area' + @cntA + ''' AS area, 
  tbl_records_temp.area' + @cntA + '
  FROM  tbl_records_temp 
  CROSS JOIN vw_current_week
  WHERE (tbl_records_temp.area' + @cntA + ' > 0)'
  --print @ssql
  exec (@Ssql)
  set @cnt = @cnt +1
end

 

[MsHart2u]

This worked perfectly!!!... Thank you for the help.

[blue]Kathy[/blue]
When you dig another out of their troubles, you find a place to bury your own.—Anonymous
The person who says it can't be done shouldn't interrupt the person who is doing it.—Chinese Proverb