Iterate DB table creation with database command

7

Hi @David_Juras,

I meant creating a project that generates 'CREATE TABLE' SQL commands based on the provided dataset. These generated commands can then be executed via the 'Custom Command' command of the 'Database Command' action.

Here’s an example of such a project. While the generalized version turned out to be less straightforward than I initially anticipated, I believe it’s configurable enough for you to use without requiring modifications. The project also contains the Test module with a usage example.

GenerateCreateTableCommand.zip (77.4 KB)

The following parameters should be provided to the project:

  • TableName - quoted or unquoted name of the created table. When the {Delimiter} parameter is provided and the {TableName} contains an unquoted name, the name will be automatically quoted by the project
  • Delimiter - should be provided if you want to generate a command with quoted table and column names
  • MinDateThreshold - Used to distinguish between numeric and Date/DateTime columns. Should be set to the smallest possible date in the exported data
  • MaxDateThreshold - Used to distinguish between numeric and Date/DateTime columns. Should be set to the greatest possible date in the exported data. When set to an empty value, the current date will be used
  • IntegerType, NonIntegerType, DateType, DateTimeType, BooleanType, TextType - database column types for corresponding detected column types (see below)

The project detects column types as follows:

  • Column is detected as Datetime when either of the following is true:
    • The column has a date or a datetime format and has at least a single datetime value (non-integer number)
    • The column has only numeric and empty values, at least a single datetime value (non-integer number) and all the non-empty values are between {MinDateThreshold} and {MaxDateThreshold} (or the current date if the parameter is empty)
  • Column is detected as Date when either of the following is true:
    • The column has a date or a datetime format, at least a single date value (integer number), and doesn't have datetime values
    • The column has only numeric and empty values, at least a single date value (integer number), doesn't have datetime values and all the non-empty values are between {MinDateThreshold} and {MaxDateThreshold} (or the current date if the parameter is empty)
  • Column is detected as Non-integer when all of the following is true:
    • it wasn't detected as a date or datetime column
    • it contains only numeric and empty values
    • it contains at least a single non-integer number
  • Column is detected as Integer when all of the following is true:
    • it wasn't detected as a date or datetime column
    • it contains only integer and empty values
  • Column is detected as Boolean when it contains only boolean and empty values
  • Column is detected as Text when it contains at least a single text value

If a column type can't be detected, the project will fail.

Feel free to ask me any questions regarding this project and the export workflow.