Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2008

    Lightbulb Unanswered: Master Data Management

    I am attempting to insert all objects (users, tables, fields, indexes, ...) from DDL scripts generated from ERDs across multiple RDBMS vendors into an MDM database created in MySQL. Are they any quick ways or utilities to generate these DML INSERT statements containing these SQL objects without the need to create a parsing application?

    Cheers, JP

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    have you tried MySQL Workbench?

    cheers | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2008
    I did and it does not generate the INSERT statement automatically: it only creates INSERTS without the actual values...

  4. #4
    Join Date
    Nov 2008

    Converting DDL script into DML inserts for all DDL objects

    From a DDL script like:

    create table `test`.`customer`(
    `cust_id` INT not null auto_increment comment 'Customer identifier',
    `organization` VARCHAR(100) not null comment 'Name of organization',
    `address1` VARCHAR(50) comment 'First part of the customer address',
    `address2` VARCHAR(50) comment 'Second part of the customer address',
    `city` VARCHAR(50) not null comment 'City where customer is located',
    `state` CHAR(2) not null comment 'State where the customer is located',
    `zipcode` CHAR(5) not null comment 'Customer 5-digit zip code',
    primary key (`cust_id`)

    I would like to insert the TABLE customer and its associated FIELDS in the following tables:

    Insert table values:

    INSERT INTO `tables` (`tbl_id`, `tbl_nm`, `schem_id`, `last_change`)
    VALUES (null ,`test`.`customer`, 1, null);

    It returns tbl_id=1

    Then insert values for all fields

    ... for the first field 'cust_id' we have:

    INSERT INTO `fields` (`field_id`, `field_nm`, `tbl_id`, `field_type`, `field_size`,
    `field_decimal`, `field_definition`, `last_change`)
    VALUES (null ,cust_id`, 1, INT, null, null, 'Customer identifier',null);

    ... for the second field 'organization' we have:

    INSERT INTO `fields` (`field_id`, `field_nm`, `tbl_id`, `field_type`, `field_size`,
    `field_decimal`, `field_definition`, `last_change`)
    VALUES (null ,organization`, 1, VARCHAR, 100, 0, 'Name of organization that the customer is part of',null);

    ... and so on for all the fields.

    I know DB studio has a similar feature, but it is done manually for all objects. We need to automateicaly propagate any changes to the real databases into the MDM repository.

    Any ideas!

  5. #5
    Join Date
    Nov 2008

    I found a way using DbUtils:

    1. Convert DDL to XML
    2. Parse XML tags into DML insert scripts

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts