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

have you tried MySQL Workbench?

http://dev.mysql.com/downloads/workbench/5.0.html

cheers

I did and it does not generate the INSERT statement automatically: it only creates INSERTS without the actual values...

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!

Gully!

I found a way using DbUtils:

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