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?
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 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.