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 clause } [...]

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 create. The name must not include a temporal specification. If the name is not qualified the table is created in the current schema. A table_name must not exist already.

  • source_table_name

    The name of the table whose definition is 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.

    • 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

      Optionally sets one or more user defined properties.

    • USING data_source

      The file format to use for the table. data_source must be one of:

      • TEXT

      • CSV

      • JSON

      • JDBC

      • PARQUET

      If you do not specify USING the format of the source table will be inherited.

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';