Copy Data from Parent Table

• 
  In simple cases where the two tables have a 1:1 relationship, it should work if for example, for Jobsite.City you choose a SQL Statement generator and connect to the target database. Enter "SELECT City FROM Client". Check Allow null values, but set the % null to 0. Finally Turn off shuffle data. This should work because of the FK relationship on ClientID (you'll need to make sure this is set), as the Client table will be populated before the Jobsite table so at run time the correct values will be returned.
  
  Alternatively, you may also be able to do this with a Python generator. I don't have an exact code to do this but this may hopefully point you in the right direction.

  __randomize__ = True
  
  import clr
  clr.AddReference("System.Data")
  from System.Data import SqlClient
  
  #Select valid employees based on Role table
  Sql = "SELECT * FROM [dbo].[Employee] WHERE [dbo].[Employee].[Role_ID] = 1"
  
  def main(config):
  
      connectionString = config["connection_string"]
      connection = SqlClient.SqlConnection(connectionString)
      command = SqlClient.SqlCommand(Sql, connection)
      validEmployeeIDs = []
  
      connection.Open()
      reader = command.ExecuteReader()
      while reader.Read():
          validEmployeeIDs.append(reader["ID"].ToString())
      connection.Close()
  
      return validEmployeeIDs
  

  
  If all else fails, however, another option is to configure your project to run a script after generation (File>Edit Project>Scripts) that will update the City column to the correct value, with something like the following:
  

  UPDATE dbo.Jobsites
  SET City = (SELECT City
  FROM dbo.Client
  WHERE Jobsites.ClientID = Client.ClientID)
  

  Jessica R January 19, 2017 19:34

  - 0 +

Add comment

Please sign in to leave a comment.