Looping Through and Inserting Records

[dleewms]

I am using SQL Server 2005.

I have a rate_category and an item_rate table. They are joined by the rate_cat field in each. The rate_category table contains unique rate_cat (varchar(12) and description (varchar(40)) fields for each record. I have two new items whose item_id (integer)values are 100078 and 100082. For each rate_cat that does NOT have a description LIKE 'UNUSABLE%', I want to add a record to the item_rate table with that rate_cat and one of the new item ids.

I've done a query to identify all rate_cat that don't have a description of 'UNUSABLE%'and 514 rows are returned.

select rate_cat
from rate_cat
where not description like 'UNUSABLE%'

I need a way to loop through each of the 514 rate_cat and insert a new record into the item_rate table for each item. When I'm finished, I would have added 1028 records to the item_rate table.

Here is an example
Rate_cat Table
Rate_Cat Description
A1234 Acme
A2345 Lowes
A3456 Home Depoe
A4567 Ace
A5678 True Value
A6789 UNUSABLE

Item_Rate Table
Rate_Cat Item_id Createdby
A1234 1000078 DLW
A2345 1000078 DLW
A3456 1000078 DLW
A4567 1000078 DLW
A5678 1000078 DLW
A1234 1000082 DLW
A2345 1000082 DLW
A3456 1000082 DLW
A4567 1000082 DLW
A5678 1000082 DLW

I'm not sure how to accomplish this. Thinking some kind of For Loop. Any help would be greatly appreciated.

Thanks

 

[Qik3Coder]

I haven't run this, but you will need something like this:

Select rc.Rate_Cat, ir.Item_ID FROM
(SELECT DISTINCT Rate_Cat FROM Rate_Cat) as rc
CROSS JOIN (SELECT DISTINCT Item_ID FROM Item_Rate) as ir) as fl
LEFT JOIN Item_Rate ir2 ON
	fl.Rate_Cat = ir2.Rate_Cat
	AND fl.Item_id = ir2.Item_ID
WHERE ir2.Item_id is null

Lodlaiden

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[dleewms]

Thanks for the response Lodlaiden. However, I don't want to add an item_rate record for every ItemID, just the two I specify. I was able to get what I wanted using the following statement as provided by another user.

Thanks!

with cte as (
select a.rate_cat,b.Item_id
from rate_cat a cross join

(select 1000078 as Item_ID
union all
select 1000082 ) b
where not a.description like 'UNUSABLE%'
)

insert into Item_Rate Table(Rate_Cat,Item_id,Createdby)
select Rate_Cat , item_id, 'DLW'
from cte