Copy Data from Parent Table

kkemeraitkkemerait Posts: 7
edited January 19, 2017 7:34PM in SQL Data Generator
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

Comments

  • Jessica RJessica R Posts: 1,319 Rose Gold 4
    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 Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


Sign In or Register to comment.