What are the challenges you face when working across database platforms? Take the survey

Incrementing, from 1, based on another column

muffinbubblemuffinbubble Posts: 2 New member
edited August 20, 2019 10:32AM in SQL Data Generator
Hi, I have a table which simplified is a sales orders lines table, with two columns, a reference to the sales order (this is a FK) and an order line. The order line column should increment from 1 to x but be unique to that sales order ID.

For example:

SalesOrderID    OrderLine
1                          1
1                          2
1                          3
2                          1
2                          2
3                          1

And so on. Currently the order line is totally random and doesn't start from 1. I can set a min/max but it needs to start from the SalesOrderID.

Is this possible? So in effect, the logic for each generated line will ask "Is this a new sales order ID, therefore the OrderLine will generate 1. Further down I randomly generate the same sales order ID, but as a row already exists once with an OrderLine of 1, increment 1 to it, so the OrderLine becomes 2. If a new sales order ID is generated which doesn't exist, we start from 1 again.".

Edit: I did try a generic Python script but it seemed to only allow you to run tests on that column and not get values of others.


  • Options
    Eddie DEddie D Posts: 1,792 Rose Gold 5
    Hi, thank you for your forum.

    From the information you have provided, I cannot identify a method to achieve your goal using the generators provided within the tool.

    One possible solution, would be to create a csv file or SQL Table that can be used as a reference for the example data you wish to generate.

    Many Thanks

    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • Options
    muffinbubblemuffinbubble Posts: 2 New member
    Hi Eddie, I managed to work around it by running a SQL script after generation (grouped on sales order ID and then ROW_NUMBER for the order line.
Sign In or Register to comment.