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

Generating IDs greater than the current max

rinsitahrinsitah Posts: 4
Hi There,

We have a PK column that is essentially an interger ID. We need to generate data for this table each day.
SDG seems to generate the same integers each time it runs, causing a PK violation.

Is there any way to make the integer start at the maximum current value of the column +1 ?

Many thanks!

Comments

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello,

    Typically in this case SQL Data Generator would just work because you would have set the column as an IDENTITY and SQL Data Generator would pick up at the next increment value.

    However, if the column does not have the identity property set, you have to use the Python generator to query the table for the latest value and then start generating numbers from the next value. Here is an example:
    # Basic generator template
    
    # Set this to be false if you want to handle
    # shuffling and randomizing the results yourself
    __randomize__ = False
    import clr
    def main(config):
        # config["column_name"] is the column name
        # config["column_type"] is the column datatype
        # config["column_size"] is the column size
        # config["n_rows"] is the number of rows
        # config["seed"] is the current random seed
        
        aIds=[]
        rowCount=0
        clr.AddReference('System.Data')
        from System.Data.SqlClient import SqlConnection,SqlCommand
        conn=SqlConnection("data source=SERVER\INSTANCE;Initial Catalog=Database;Integrated Security=SSPI")
        sqlcmd="SELECT MAX("+config["column_name"]+") FROM table_1"
        comm=SqlCommand(sqlcmd,conn)
        conn.Open()
        reader=comm.ExecuteReader()
        try:
            reader.Read()
            rowCount=int(reader.GetInt32(0))
        except:
            rowCount=0
        conn.Close()
        iterator=0
        while (iterator < config["n_rows"]):
            rowCount=rowCount+1
            aIds.append(rowCount)
            iterator=iterator+1
        return aIds
    
  • Options
    Thanks for that.

    This works mostly.
    Its ok for normal ints, but when we have a BigInt ID Column, it fails:

    Next ID would be 50000000510593201

    So I changed it to use an int64:
    rowCount=int(reader.GetInt64(0))

    The correct values then appear on SDG, but they are Orange and have a yellow exclimation mark on the column.

    When I try to run the generate, it says
    Value '50000000510593201' is the wrong data type for column ID

    How do I get it to insert BigInt Columns?
  • Options
    I was afraid this was going to happen... SDG is a 32-bit app so it can't generate a number this big through the Python generator.
  • Options
    Looks like you can use .NET system types... try this:
    # Basic generator template
    
    # Set this to be false if you want to handle
    # shuffling and randomizing the results yourself
    __randomize__ = False
    import clr
    def main(config):
        # config["column_name"] is the column name
        # config["column_type"] is the column datatype
        # config["column_size"] is the column size
        # config["n_rows"] is the number of rows
        # config["seed"] is the current random seed
        
        aIds=[]
        rowCount=0L
        clr.AddReference('System.Data')
        import System
        from System.Data.SqlClient import SqlConnection,SqlCommand
        conn=SqlConnection("data source=PS-BRIAND\SQL2008R2;Initial Catalog=64121;Integrated Security=SSPI")
        sqlcmd="SELECT MAX("+config["column_name"]+") FROM table_1"
        comm=SqlCommand(sqlcmd,conn)
        conn.Open()
        reader=comm.ExecuteReader()
        try:
            reader.Read()
            rowCount=long(reader.GetInt64(0))
        except:
            rowCount=0L
        conn.Close()
        iterator=0L
        while (iterator < config["n_rows"]):
            rowCount=rowCount+1
            aIds.append(System.Int64(rowCount))
            iterator=iterator+1
        return aIds
    
  • Options
    That works great!

    Thanks very much.
Sign In or Register to comment.