how to do a FOR EACH loop in T-Sql using join?

Question

I have a table PRODUCT that is basically set up so there is an PRODUCTID, PRODUCTNAME... it looks sort of like this

PRODUCTID  PRODUCTNAME
100        PNAME1   
101        PNAME2         
102        PNAME3

Now I have to insert a record into new table PRODUCTMAPPING for each row in the PRODUCT.

so my new table PRODUCTMAPPING should look like this

PRODUCTMAPPINGID  PRODUCTID
1                 100
1                 101
1                 102
2                 100
2                 101
2                 102
3                 100

and so on ....

I tried doing while but I need it using Join.

Can we acheive this using joins ?

Thanks in advance.

How do you mapping your product ? What is the relation between these two ? — Mahesh, Feb 26 '15 at 11:49
possible duplicate of [SQL Server FOR EACH Loop](http://stackoverflow.com/questions/10300414/sql-server-for-each-loop) — Rajesh, Feb 26 '15 at 11:51
If you have your PRODUCTMAPPINGID values somewhere, you can just cross join that with PRODUCT — James Z, Feb 26 '15 at 11:54

score 1 · Answer 1 · answered Feb 26 '15 at 12:04

1

One way;

select
    row_number() over(partition by a.PRODUCTID order by a.PRODUCTID) PRODUCTMAPPINGID,
    a.PRODUCTID
from PRODUCT a, PRODUCT b

answered Feb 26 '15 at 12:04

Alex K.

171,639
30
264
288

Old style join is not ideal. – Pixelated Feb 26 '15 at 12:06
This just seems to be a one off thing – Alex K. Feb 26 '15 at 12:07

Rajesh · Answer 2 · 2015-02-26T12:00:31.197

0

Using LOOP

The following example specifies that the JOIN operation in the query is performed by a LOOP join.

Select sp.Name,spqh.quota 
FROM Sales.SalesPersonQuotaHistory AS spqh
    INNER LOOP JOIN Sales.SalesPerson AS sp
    ON spqh.SalesPersonID = sp.SalesPersonID
WHERE sp.SalesYTD > 2500000.00;
GO

Refer this MSDN link

edited Feb 26 '15 at 12:00

answered Feb 26 '15 at 11:55

Rajesh

1,600
5
33
59

Sorry to all I changed my Full answer – Rajesh Feb 26 '15 at 12:01

score 0 · Answer 3 · answered Feb 26 '15 at 12:08

INSERT
INTO    dbo.PRODUCTMAPPING
        (
        PRODUCTMAPPINGID
        ,PRODUCTID
        )

SELECT  pmv.PRODUCTMAPPINGID
        ,p.PRODUCTID
FROM    dbo.Product p
CROSS JOIN
        (
        SELECT  pm.ProductMappingID
        FROM    dbo.ProductMappingValues pmv -- WHERE DO THESE COME FROM?
        ) pmv

how to do a FOR EACH loop in T-Sql using join?

3 Answers3