Generating IDs greater than the current max

rinsitah

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!

Brian Donahue

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

rinsitah

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?

Brian Donahue

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.

Brian Donahue

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

rinsitah

That works great!

Thanks very much.