Generate CREATE TABLE SQL Statements

For a given table and set of columns a CREATE TABLE statement is generated, including PRIMARY KEY constraint. By default all identifiers in the statement are quoted for generic ANSI SQL compliance, and backend-specific quoting will be used if a connection object is provided.

sql_create_table_ztrax(ZType, TableName, KeepColumns = NULL,
  .con = DBI::ANSI())

Arguments

ZType	Either "ZTrans" or "ZAsmt".
TableName	Name of table to be added to the database, as it appears in documentation and raw data file names (UpperCamelCase).
KeepColumns	A vector of names of columns to be included in table on the database, as they appear in documentation (UpperCamelCase). If not provided all columns in the table are included.
.con	A DBIConnection object, as returned by DBI::dbConnect(). If not provided DBI::ANSI() is used to generate a statement that is ANSI SQL compliant.

Value

A DBI::SQL() CREATE TABLE SQL statement. The SQL statement is also printed to the console as a message and copied to the clipboard.

Examples

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ":memory:")

sql_create_table_ztrax("ZTrans", "Main", c("TransID", "SalesPriceAmount"), con)
#> CREATE TABLE `ztrans_main` (
#>   `trans_id` bigint,
#>   `sales_price_amount` decimal,
#>   PRIMARY KEY (`trans_id`)
#> )

# If a set of columns are not provided, all of the tables columns will be included.
sql_create_table_ztrax("ZAsmt", "TaxExemption")
#> CREATE TABLE "zasmt_tax_exemption" (
#>   "row_id" char(36),
#>   "tax_exemption_stnd_code" char(2),
#>   "fips" char(5),
#>   "batch_id" int,
#>   PRIMARY KEY ("row_id", "tax_exemption_stnd_code")
#> )

# If a connection object is not provided DBI::ANSI() is used to generate an
# ANSI-SQL compliant statement
sql_create_table_ztrax("ZTrans", "Main", c("TransID", "SalesPriceAmount"))
#> CREATE TABLE "ztrans_main" (
#>   "trans_id" bigint,
#>   "sales_price_amount" decimal,
#>   PRIMARY KEY ("trans_id")
#> )