Repeat a set of values per another column generated

HI,
I've one table that a want to generate some that the stores a set of states by a key (another column). 
i.e. to each new key generated I want to insert 4 states, e,g,
Key | Value
1 | 1
1| 2
3| 3
4| 4
2 | 1
2| 2
2| 3
2| 4
3|| 1
3| 2
3| 3
3| 4

so I need to generate 200.000 keys with 4 states, a total of 800.000 rows. I know how to do it using T-SQL, but because it's the table has other columns that I need to generate I want to use the data generator and use this generation as part of a project.

As a general question: How can I generate new columns implementing rules that depend on other columns in the same Table?

Thanks very much, Luis



Tagged:

Best Answer

  • Alex BAlex B Posts: 918 Diamond 3
    edited August 3, 2020 8:38AM Accepted Answer
    Hi @LuisQ ,

    You'll need to use a Python script generator to accomplish this, to be able to access the values of another column, though depending on what the keys and state actually are you might be able to do both with a Python script generator.

    I've just replicated your example using the following though I understand it is an example and may not be numbers as easily produced as this:

    Python script generator for "key" column defined as:
    def main(config):
        
        mySet = []
        
        for x in range(1,200000):
            for y in range(4):
                mySet.append(x)
        
        return mySet
    

    Python script generator for "state" column defined as:
    def main(config):
       
        return [1, 2, 3, 4]

    To reference another column in a python script you just type the name of the column (or use the "Insert Column Name..." button below the Script box.  For this, you would need to return a set or list of values (like the top example) rather than have a loop return one value at a time

    I hope that helps!
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?

Answers

  • LuisQLuisQ Posts: 2 New member
    Excellent  @Alex B, thanks for your quick and valid reply, it works fine... so it means that I can use always Python to create rules that relate columns in the same table, right? example if column A = 1 then column B = "AAAA" else  column B = "BBBBB" end. do you have any example? Sorry I'm very new using the generator ;)
    Cheers, Luis
  • Hi Luis,

    You could do that sort of thing with if/elif/else in Python on the Column B generator.  It would look something like:
    def main(config):
    
        if key == 1:
            return 'AAA'
        elif key == 2:
            return 'BBB'
        else:
            return 'CCC'

    The elif is only if you have more than one thing you want to specify a value for and the else covers all the others.

    There are a lot of resources on writing code in Python (for example w3schools https://www.w3schools.com/python/python_conditions.asp ) and they should help with writing Python scripts for this generator.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
Sign In or Register to comment.