Extracting the DDL

[cmcclellan]

I have a very unique problem - I need to extract the DDL and save along with the data in the workflow.

Attached are 2 files, the CSV and the workflow to process it - basically to set the datatypes to the proper format.

But obviously saving as CSV means that you lose the datatypes that have been defined.

I understand that I really need to write to a database in order to get the proper DDL, but is there a way to get generic/ANSI SQL - something that might work as a CREATE TABLE in (almost) any RDBMS ?

[danilang · Accepted solution]

Hi @cmcclellan

While there may be an automated way to do this type of thing that i'm not aware of, I've always built up the required SQL as a string.  I'd start with the Field Info tool to get the required metadata for the dataset and then add in the correct boiler plate for ANSI-SQL Create, Insert, Update etc.

Dan

[cmcclellan]

That's EXACTLY what I was looking for :) :) 

I've used Field Info before, but for some reason I just didn't think of it in this case :)

[dipika07011988]

how do we get the collation info as well?

[danilang]

Hi @dipika07011988 

You can build generic SQL DDL statements by looking at the metadata of the fields that you want to write.  To determine the Collation order for specific fields, you need to look at the data in the fields.  What character set would be required to support all the character values that you find in your data?  I don't know of a way to do this automatically.  I've always done this manually process by scanning the data.  Once you do determine it, you can build a mapping table that links fields in tables with the proper collation syntax and join this to your field info to build up the correct syntax

Dan