SQL Stored Procedure?

Question

Hello all:

I am working on a project and need to create 100000000 records and they have a small data footprint, just 4 columns, 3 uniqueidentifiers, 1 int. 2 of the identifiers need to be read from other tables, and then used in the other table. So for example

TableA has a column taid uniqueidentifier (about 20000 records)
TableB has a column tbid uniqueidentifer (about 5000 records)

I need a row in TableC for every row in TableA and TableB, that will look like this:
taid tbid tcid int

Is it possible to a stored procedure to retrieve the ids from TableA and TableB, store in arrays or something, and then do loops to create TableC?

Thanks!

Answer 1

If we overlook that this may be more than mouthful, you can do something like:

INSERT table(taid, tbid, tcid, int)
   SELECT a.taid, b.tbid, newid(), row_number() over(ORDER BY (SELECT NULL))
   FROM  tableA a
  CROSS JOIN tableB b

Your description is somewhat vague, so I had to extrapolate a little bit.

Answer 2

Adding on to Erland's answer, if inserting 100M at once is too much for your environment to handle, you could run multiple inserts to batch the operation. For example, the WHERE clauses below would select/insert approximately 25M rows each, assuming the tcid uniqueidentifer distribution is random.

WHERE 
    a.tcid < '00000000-0000-0000-0000-400000000000';

WHERE 
    a.tcid >= '00000000-0000-0000-0000-400000000000'
    AND a.tcid < '00000000-0000-0000-0000-800000000000';

WHERE 
    a.tcid >= '00000000-0000-0000-0000-800000000000'
    AND a.tcid < '00000000-0000-0000-0000-c00000000000';

WHERE 
    a.tcid >= '00000000-0000-0000-0000-c00000000000';