Generating IDs greater than the current max

The simplest way to populate your database with test data.

Moderators: JonathanWatts, David Atkinson, Anu Deshpande, Michelle Taylor, Lionel, Emma A, sherr

Generating IDs greater than the current max

Postby rinsitah » Thu Aug 30, 2012 12:45 pm

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!
rinsitah
 
Posts: 3
Joined: Thu Aug 30, 2012 12:42 pm

Postby Brian Donahue » Fri Aug 31, 2012 2:14 pm

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:
Code: Select all
# 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
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby rinsitah » Mon Sep 03, 2012 1:56 pm

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?
rinsitah
 
Posts: 3
Joined: Thu Aug 30, 2012 12:42 pm

Postby Brian Donahue » Mon Sep 03, 2012 3:43 pm

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
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby Brian Donahue » Mon Sep 03, 2012 4:10 pm

Looks like you can use .NET system types... try this:
Code: Select all
# 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
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby rinsitah » Wed Sep 05, 2012 9:47 am

That works great!

Thanks very much.
rinsitah
 
Posts: 3
Joined: Thu Aug 30, 2012 12:42 pm


Return to SQL Data Generator 2

Who is online

Users browsing this forum: No registered users and 0 guests