Excluding columns

softworksgroup

I am a new user so I apologize if this question has an obvious answer.

In many of the tables I want to generate, the best value for a column is provided by a default defined in the database itself. How can I instruct the generator to ignore columns so that the database default will be applied. (The columns are defined NOT NULL so I cannot set the %Null attribute to 100%).

Thanks

Brian Donahue

Hi,

I have found a way to force some NULL values in using the Python generator, but I still can't get this to work for me. I think it's because SQL Data Generator uses BULK INSERT and you need some kind of format file to allow the bulk insert to jam in NULLs. Here is the Python script anyway in case you want to give it a try...

# shuffling and randomizing the results yourself
import System;
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
    from System import Array
    nullArray=Array.CreateInstance(System.DBNull,config["n_rows"])
    return (nullArray)

cvillegas

Is there an actual solution to this or an update that will be created with the ability to ignore columns in a table when generating data?

Brian Donahue

I've provided a solution that will insert NULL into the column, just need a SQL Server professional to get me around the limitation in bulk insert... If there is an answer to that it will work. If not I don't think SQL Data Generator can support it.

Brian Donahue

Ah, maybe not. I checked the source code, and it explicitly sets the Bulk Copy option "KeepNulls", so when SDG inserts null it overrides the default that would normally happen if you tried to insert a null value from a SQL query.

So I guess you have a feature request there. The code needs to be changed to make this work.

Brian Donahue

I have logged an enhancement request for this: SDG-988 - provided changing the KeepNulls property doesn't break other parts of the program, it should be possible to do.