Iterate and create DB tables

I want to iterate through a folder of QVDs and export them to a DB

How can I create the tables if they don’t exist through iteration?

Red

Hi Red,

Use the “Database Command” transformation to create a database table before the iteration. Make sure it does nothing in case the table already exists so that it doesn’t fail on repetitive runs.

image

If the create table command is inside an iterator project, will the table columns get created automatically?
Project:

  1. Get file names
  2. filter for .QVDs
  3. Tablename = filename(no extension)
  4. Start iterator (Filename,Tablename,DB name)
  5.  Import file{Filename}
    
  6.  Create DB.Table {Tablename} if not exists
    
  7.  Upload DB.Table {TableName}
    

Can #6 occur from a file passed in as a parameter?
Will it generate the correct columns?

Red

No, it won’t. Database columns must have a data type assigned, therefore data types have to be declared explicitly for table creation.

If you find a way to identify column data types in arbitrary QVD file then a custom SQL statement for table creation can be composed and executed in EasyMorph using the “Custom SQL” mode of the “Database command” transformation.

Thanks