Create a Table in SQL Analytics EndPoint

[JohnAG]

Hey guys,
I'm new to SQL Analytics EndPoint under Microsoft Fabric, I have used Dataflow Gen2 to transform an excel file sitting in Lakehouse then sink the data in SQL Analytics EndPoint (Data Warehouse).
The data got added successfully under a table dbo.Response 
However, I want the data to go under schema STR_Monthly_Excel.Responses

I found this script online to clone the table to a different schema, but it didn't work. 
What am I doing wrong? 
Is there a better way to create the STR_Monthly_Excel.Responses table?

Please see the image below and thank you for your help. 

[Anonymous · Accepted solution]

Hi @JohnAG ,

Thanks for using Fabric Community.

As I understand you would like to create a new schema in Lakehouse.

You can try using below code to transfer the schema, but I believe this is not a recommended approach.

ALTER SCHEMA newschemaname TRANSFER dbo.yourtablename

It seems to have some limitations when we use different schema.

Reference Text:

Custom SQL objects in lake databases

Lake databases allow creation of custom T-SQL objects, such as schemas, procedures, views, and the inline table-value functions (iTVFs). In order to create custom SQL objects, you MUST create a schema where you will place the objects. Custom SQL objects cannot be placed in dbo schema because it is reserved for the lake tables that are defined in Spark, database designer, or Dataverse.

Link :
https://learn.microsoft.com/en-us/azure/synapse-analytics/metadata/database 

Creation of new schema is recommended in Data Warehouse but not in Lakehouse

Tables in data warehousing - Microsoft Fabric | Microsoft Learn