How to create a SQL table inside the project path instead of outside in Azure datalake from databricks?

Question

I am working on a project in which I want to create a SQL table and save it a project path. Here is how my project path looks like this.

abfss://dev@xyz.dfs.core.windows.net/hkay/project_name/

When I use the below SQL code in databricks, it saves the data in this folder instead of the project path given above. I tried using location {project_path} inside create statement but it failed due to wrong syntax.

spark.sql(f"""
  CREATE OR REPLACE TABLE  {database}.table_name
  SELECT * FROM {database}.table_name_temp
  WHERE 1=0
""")

This creates a folder in abfss://dev@xyz.dfs.core.windows.net/hkay/ outside the project path which I don't want. Any idea?

Edit 1:

This is what I was using. Not sure if the syntax is correct.

spark.sql(f"""
  CREATE {database}.table_name
  LOCATION 'abfss://dev@xyz.dfs.core.windows.net/hkay/project_name/'
  SELECT * FROM {database}.table_name_temp
  WHERE 1=0
""")

Answer 1

If you just want to create an empty table with the structure as another table, then you need to use slightly different syntax (see docs) - note the AS clause:

CREATE database.table_name
  USING delta
  LOCATION 'abfss://dev@xyz.dfs.core.windows.net/hkay/project_name/'
  AS SELECT * FROM database.table_name_temp LIMIT 0