| Author |
Message |
rinsitah
Joined: 30 Aug 2012
Posts: 3
|
 Posted: Thu Aug 30, 2012 12:45 pm Post subject: Generating IDs greater than the current max |
 |
|
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 |
|
 |
Brian Donahue
Joined: 23 Aug 2004
Posts: 6341
Location: Red Gate Software
|
| Posted: Fri Aug 31, 2012 2:14 pm Post subject: |
|
|
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 |
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
rinsitah
Joined: 30 Aug 2012
Posts: 3
|
| Posted: Mon Sep 03, 2012 1:56 pm Post subject: |
|
|
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 |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6341
Location: Red Gate Software
|
| Posted: Mon Sep 03, 2012 3:43 pm Post subject: |
|
|
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
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6341
Location: Red Gate Software
|
| Posted: Mon Sep 03, 2012 4:10 pm Post subject: |
|
|
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 |
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
rinsitah
Joined: 30 Aug 2012
Posts: 3
|
| Posted: Wed Sep 05, 2012 9:47 am Post subject: |
|
|
That works great!
Thanks very much. |
|
| Back to top |
|
|
|
