Iterating Through Table in High-Performance Code

Question

Supposing I have two temporary tables

CREATE TABLE TableA
(
    SomeValue    NVARCHAR(64)
)

CREATE TABLE TableB
(
    SomeValue    NVARHCAR(64)
)

and a final table

CREATE TABLE TableC
(
    SomeValue1    NVARCHAR(64),
    SomeValue2    NVARHCAR(64)
),

what is the best way to insert into TableC every possible combination of values from TableA and TableB in a high-perfomance fashion? I know cursors must be the least thing to think about, but will two WHILE loops do it fast enough?

How many rows in A and B? Do you want it atomic/transaction? — gbn, Jun 09 '11 at 10:01

score 3 · Accepted Answer · answered Jun 09 '11 at 09:48

3

A simple Cartesian product which is a CROSS JOIN (Wikipedia, MSDN)

INSERT TABLEC 
   (SomeValue1, SomeValue2)
SELECT
   TABLEA.SomeValue, TABLEB.SomeValue
FROM
   TABLEA CROSS JOIN TABLEB

answered Jun 09 '11 at 09:48

gbn

422,506
82
585
676

Iterating Through Table in High-Performance Code

1 Answers1

Linked