T-SQL inserting record while loop

Question

I want to insert a record into another table (ten_split) until the value exceeds a value in the End_loc field from the start_table. The idea is to split the records in the start_table into 10m segments.

start_table looks like below:

ID   Start_loc   End_loc
 1    0           40
 2    0           35

I want the ten_split table to look like below based on the start_table:

UID  ID  start_new  end_new
 1    1   0          10
 2    1   10         20
 3    1   20         30
 4    1   30         40
 5    2   0          10
 6    2   10         20
 7    2   20         30
 8    2   30         40

I am using Microsoft T-SQL. I am new to using loops and would appreciate any help.

Steve Ford · Accepted Answer · 2016-02-15 09:39:46Z

1

Assuming I'm understanding your question correctly, since you're using sql server, you can use a recursive cte:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

create table start_table (id int, start_loc int, end_loc int);

insert into start_table values (1, 0, 40);
insert into start_table values (2, 0, 35);

Query 1:

with recursivecte as (
  select id, 0 as start_loc, 10 as end_loc
  from start_table
  union all 
  select s.id, r.start_loc+10, r.end_loc+10
  from start_table s
    join recursivecte r on s.id = r.id
  where r.end_loc < s.end_loc
  )
select * from recursivecte
order by id, start_loc

Results:

| id | start_loc | end_loc |
|----|-----------|---------|
|  1 |         0 |      10 |
|  1 |        10 |      20 |
|  1 |        20 |      30 |
|  1 |        30 |      40 |
|  2 |         0 |      10 |
|  2 |        10 |      20 |
|  2 |        20 |      30 |
|  2 |        30 |      40 |

edited Feb 15, 2016 at 9:39

Steve Ford

7,81323 silver badges42 bronze badges

answered Feb 15, 2016 at 2:39

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Steve Ford Over a year ago

@Benjie98 I only edited the post to reformat, you should accept the answer as the accepted answer so sgeddes gets the credit

Collectives™ on Stack Overflow

T-SQL inserting record while loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related