Create a table name without back tick when using set variable

[akshayauser]

When i tried to create a table name with variable like this

-- Set a string variable
SET table_suffix = 'suffix';

-- Use dynamic SQL to create a table with the variable as a suffix in the table name
CREATE TABLE IF NOT EXISTS <dbname>.my_table_${table_suffix} (
    id INT,
    name STRING,
    age INT
)

I could able to fetch the data from this table like this.

select * from gmi_im.my_table_`suffix`

But I need it without back ticks . Is there any po way?

[high-energy]

I'm not that familiar with SQL variables. I can use them in queries, but can't seem to get the DDL to use them.

What you are trying to do is simpler in python. See below for an example.

table_suffix = 'suffix';

spark.sql(f"""
            CREATE TABLE IF NOT EXISTS shawn.default.table_with_{table_suffix} (
            id INT,
            name STRING,
            age INT
        )
        """ )

spark.sql(f"""
            INSERT INTO shawn.default.table_with_{table_suffix}
            VALUES
            (1, 'Batman', 30),
            (2, 'Robin', 20)
        """ )

df = spark.read.table(f"shawn.default.table_with_{table_suffix}")

display(df)

[brockb]

Hi,

It's possible that the `identifier` clause is what you're looking for (https://docs.databricks.com/en/sql/language-manual/sql-ref-names-identifier-clause.html#identifier-c...). If so, this basic example should work:

DECLARE mytab = '`mycatalog`.`myschema`.t1';
DECLARE mytab_suffix = 'suffix';
CREATE TABLE IF NOT EXISTS IDENTIFIER(mytab || '_' || mytab_suffix) (c1 int);
SELECT * FROM IDENTIFIER(mytab || '_' || mytab_suffix);

Hope it helps.