Inserts within Strored Procedure

Database
Enthusiast

Inserts within Strored Procedure

Hi, We have a table (T1) that contains ranges (~25K ranges) that needs to be converted into individual value i.e. breaking range and inserting into a new table (T2).

fields are separated with commas

T1

Name1, 10,15, Add1

Name2, 20,25, add2

We need to create, individual rows for each range. Actual ranges are very large (upto million) and total rows would be 10M from 25K different ranges.

T2

Name1, 10,15, Add1,11

Name1, 10,15, Add1,12

Name1, 10,15, Add1,13

Name1, 10,15, Add1,14

Name1, 10,15, Add1,15

Name1, 20,25, Add1,21

Name1, 20,25, Add1,22

Name1, 20,25, Add1,23

Name1, 20,25, Add1,24

Name1, 20,25, Add1,25

We thought of two options.

  1. A stored procedure reading T1 and inserting rows into T2. Even by using transaction (BT and ET), its slow. 50K rows in T2 takes more than 2 minutes.
  2. Recursive SQL, but found it slow too.

I would prefer option # 1 as there would be additional logic. But open for suggestion.

Please advice.