Setting custom mappings on multiple tables

JVWayJVWay Posts: 8
edited July 18, 2007 3:13AM in SQL Toolkit Previous Versions
(I posted this in the SQL compare forum with no response. Though there's little action in this forum I thought I'd try here.)

I have a database with multiple tables that need custom mappings.
Most of the tables in the database do have legitimate mappings so
I do a SchemaMappings mappings = new SchemaMappings();
mappings.CreateMappings(fromDB, toDB);

But there are still 14 tables that need custom mappings.

I don't see how I can add multiple custom mappings to a SchemaMappings object. I'd really rather not have to create table mappings for all 47 tables in the database.

I'll keep working on it but any suggestions appreciated.

Thanks,
Jerry

Comments

  • Try using the TableMappings.CreateMappings(fromDB.Tables, toDB.Tables) method directly or use the TableMappings property of the relevant SchemaMapping.

    e.g. mySchemaMapping.TableMappings.Join(fromDB.Tables["[dbo].[table1]"],toDB.Tables["[dbo].[table2]"]);

    Not sure I've got the syntax bang on but hopefully that'll give you the idea.
    Richard Mitchell
    Project Manager
    Red Gate Software Ltd
  • Thanks,

    But I've still got a bit of a problem. This is the code I'm using.

    SchemaMappings mappings = new SchemaMappings();
    mappings.CreateMappings(fromDB, toDB);
    TableMapping tableMapping = (TableMapping)mappings.TableMappings.Join(fromDB.Tables["[dbo].[deleted_responses]"], toDB.Tables["[dbo].[deleted_responses]"]);
    mappings.TableMappings.Add(tableMapping.FieldMappings["responseid"]);

    When I try to add the FieldMapping, which I understand to define the custom mapping, I get the following error:
    TableMappings only supports TableMapping objects

    My problem with using the TableMappings method directly is that I would need to do that for 47 tables in the database and I'd like a more efficient method if it's available.
    So, am I missing something about adding the FieldMapping to the SchemaMapping?

    Thanks for your help on this



    richardjm wrote:
    Try using the TableMappings.CreateMappings(fromDB.Tables, toDB.Tables) method directly or use the TableMappings property of the relevant SchemaMapping.

    e.g. mySchemaMapping.TableMappings.Join(fromDB.Tables["[dbo].[table1]"],toDB.Tables["[dbo].[table2]"]);

    Not sure I've got the syntax bang on but hopefully that'll give you the idea.
  • There is the property FieldMappings on a TableMapping class which you can Join() with fields from the table from either side of the database.

    And to anticipate your next question there is MatchingMappings on a TableMapping object which contains the fields which are used to compare the tables. If there isn't a primary key or unique index that can be used by default you can set a custom comparison key by adding FieldMapping objects for that TableMapping into the MatchingMappings. After you've added the fields you want be sure to call RefreshMappingStatus so that you can confirm comparison can take place. You can also use a specific index via UseIndexForMapping on the TableMapping, probably doesn't hurt to call RefreshMappingStatus after UseIndexForMapping either.

    The full help for the TableMapping class can be found here
    Richard Mitchell
    Project Manager
    Red Gate Software Ltd
Sign In or Register to comment.