Generating IDs greater than the current max

• 
  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
  

  Brian Donahue August 31, 2012 09:14

  - 0 +
• 
  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 September 03, 2012 08:56

  - 0 +
• 
  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 September 03, 2012 10:43

  - 0 +
• 
  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
  

  Brian Donahue September 03, 2012 11:10

  - 0 +
• 
  That works great!
  
  Thanks very much.

  rinsitah September 05, 2012 04:47

  - 0 +

Add comment

Please sign in to leave a comment.