Competition: What’s your favorite Redgate tool? Enter now.

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?

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hello,

    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.
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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"
        sql_connection=System.Data.SqlClient.SqlConnection(str_connection)
        str_command="SELECT TextField FROM Table1 WHERE TextField LIKE '%"
        str_command+=COUNTRYNAME # Where COUNTRYNAME is a field from the table
        str_command+="%'"
        sql_command=System.Data.SqlClient.SqlCommand(str_command)
        sql_command.Connection=sql_connection
        sql_connection.Open()
        sql_datareader=sql_command.ExecuteReader()
        a_value=""
        while sql_datareader.Read():
            a_value=sql_datareader.GetString(0)
        sql_datareader.Close()
        sql_connection.Close()
        return [a_value]
    
  • 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.