Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Extracting the DDL

cmcclellan
13 - Pulsar

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 ?

4 REPLIES 4
danilang
19 - Altair
19 - Altair

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
13 - Pulsar

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
6 - Meteoroid

how do we get the collation info as well?

danilang
19 - Altair
19 - Altair

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

Labels