Red Gate forums :: View topic - Generating IDs greater than the current max
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Data Generator 2
SQL Data Generator 2 forum

Generating IDs greater than the current max

Search in SQL Data Generator 2 forum
Post new topic   Reply to topic
Jump to:  
Author Message
rinsitah



Joined: 30 Aug 2012
Posts: 3

PostPosted: Thu Aug 30, 2012 12:45 pm    Post subject: Generating IDs greater than the current max Reply with quote

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!
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6669

PostPosted: Fri Aug 31, 2012 2:14 pm    Post subject: Reply with quote

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:
# 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
Back to top
View user's profile Send private message
rinsitah



Joined: 30 Aug 2012
Posts: 3

PostPosted: Mon Sep 03, 2012 1:56 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6669

PostPosted: Mon Sep 03, 2012 3:43 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6669

PostPosted: Mon Sep 03, 2012 4:10 pm    Post subject: Reply with quote

Looks like you can use .NET system types... try this:
Code:
# 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
Back to top
View user's profile Send private message
rinsitah



Joined: 30 Aug 2012
Posts: 3

PostPosted: Wed Sep 05, 2012 9:47 am    Post subject: Reply with quote

That works great!

Thanks very much.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group