What are the challenges you face when working across database platforms? Take the survey

populate field from SQL lookup?

amccolloughamccollough Posts: 5
edited January 4, 2015 11:59PM in SQL Data Generator
I'm working on a data generator project where I would like to be able to do a query against a table containing lookup data.

You know how when scripting data generation fields, other fields are represented like $[Division]? I'd like to be able to do a SQL Query like this: "
SELECT BusinessUnit FROM tblBusinessUNITS where Divsion = '$[Division]'
" and have the result (presuming there would be one record returned) be the data for that column/row instance.

Is this possible?


  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1

    SQL Data Generator has the ability to run a Python script, so if you have a Python script, variable declarations typically begin with the dollar-sign ($). The question must be, how to get a generated column value and use that in a SQL query. This sounds like you would need a combination of two generators - cross-column and SQL statement.

    The best solution I can think of for this is to use the Python generator, get the column name into a variable and use Python to get the data.
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    I came up with this, but it needs to make a connection to the server for every record.
    # Basic generator template
    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
        import System
        import System.Data
        import System.Data.SqlClient
        str_connection="Data Source=localhost;Initial Catalog=Database2;Integrated Security=SSPI"
        str_command="SELECT TextField FROM Table1 WHERE TextField LIKE '%"
        str_command+=COUNTRYNAME # Where COUNTRYNAME is a field from the table
        while sql_datareader.Read():
        return [a_value]
  • Options
    Say I have a table participants with PK part_id and another table DeliveryAddress with foreign key part_id.

    If i setup the foreign key on DeliveryAddress back to the participants table then it will only select unique part_id when a participant could have multiple delivery addresses.

    There is a hint to the right of the "Population method" on the Foreign key generator UI stating: If the foreign key references a single column, each value in the referenced column is only used once. I have tested this and it creates a one to one foreign key.
    We offer guaranteed success for a+ training exam with help of latest comptia network+ and security plus study guide practice qustions and the exams of wwwclarku.edu ccie security Southwestern College
Sign In or Register to comment.