CREATE TABLE LIKE (Databricks SQL)

Defines a table using the definition and metadata of an existing table or view.

Delta Lake does not support CREATE TABLE LIKE. Instead use CREATE TABLE AS.

Syntax

CREATE TABLE [ IF NOT EXISTS ] table_name LIKE source_table_name [table_clauses]

table_clauses
   { USING data_source |
     LOCATION path |
     TBLPROPERTIES ( property_key [ = ] property_val [, ...] ) } [...]

property_key
  { identifier [. ...] | string_literal }

Parameters

  • IF NOT EXISTS

    If specified ignores the statement if the table_name already exists.

  • table_name

    The name of the table to be created. The name must not include a temporal specification. If the name is not qualified the table is created in the current database. A table_name must not exist already.

  • source_table_name

    The name of the table which’s definition is to be copied. The table must not be a Delta Lake table.

  • table_clauses

    Optionally specify a data source format, location, and user defined properties for the new table. Each sub clause may only be specified once.

    • USING data_source

      The file format to use for the table new data_source must be one of TEXT, CSV, JSON, JDBC, PARQUET, or ORC. If you do not specify USING the format of the source table will be inherited.

    • LOCATION path

      Path to the directory where table data is stored, which could be a path on distributed storage. If you specify a location the new table becomes an external table. If you do not specify a location the table is a managed table.

    • TBLPROPERTIES

      Sets one or more user defined properties of an existing view. Properties are key value pairs.

      • property_key

        The property key. The key can consist of one or more identifiers separated by a dot, or a string literal.

        Property keys must be unique.

      • property_val

        The new value for the property. The value must be a BOOLEAN, STRING, INTEGER, or DECIMAL literal.

Examples

-- Create table using a new location
> CREATE TABLE Student_Dupli LIKE Student LOCATION '/mnt/data_files';

-- Create table like using a data source
> CREATE TABLE Student_Dupli LIKE Student USING CSV LOCATION '/mnt/csv_files';