Copy Data from Parent Table

kkemerait

I have a table called Client which has a Child table called Jobsite. When I auto-Generate what I want is that for each child row inserted I want it to COPY the column called CITY in the Parent Table into the Child Table (same column name) so that each child row has the SAME value for CITY as its Parent Table (Client) does.

I have tried Manual FK I have also tried writing a direct SQL statement, but while they USE the Column from Client the child records do not necessarily match the parent Record, so I end up with something like this:

CLIENT (parent)
100, Hartford, ...
101, NYC, ...
102, San Francisco

JOBSITE (child)
100, 102, NYC...
101, 100, San Francisco...
102, 101, Hartford

While what I want is this:

CLIENT (parent)
100, Hartford, ...
101, NYC, ...
102, San Francisco

JOBSITE (child)
100, 102, San Francisco...
101, 100, Hartford...
102, 101, NYC

I have turned off the shuffle data checkbox as well but to no avail. Can anyone help with this.

Thanks in advance

Jessica R

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)