none
Create external tables in ADLS

    Question

  • Hi,

    we have a database with several hundreds tables in Azure SQL. Is there a way/tool to create all this external tables in ADLS automatically.

    We could create a U-SQL file dynamically - but this is a lot of work and might be a feature that everybody needs.

    To create U-SQL dynamically: is there a way to execute generated U-SQL like "sp_executesql" in T-SQL?

    Regards
    Jörg

    Wednesday, January 27, 2016 6:11 AM

Answers

  • Hi Jörg

    Re accessing tables in Azure SQL:

    We currently do not have such a tool, although I would think that one could write one with some powershell commands to extract the schema from Azure SQL, do the data type mapping and then create the script doing the external table definitions in U-SQL.

    However, if you have such a large amount of tables, do you really need to create an external table? Would the following not work as well?

    @patients= 
    SELECT * 
    FROM EXTERNAL master.SQL_PATIENTS LOCATION "dbo.patients"; 

    That way you can just query the table names when you need them and only register the data source.

    And U-SQL does not provide procedural extensions so there is no sp_executesql. However, as seen above (and with pass through queries), you can be more dynamic in your queries and avoid having to create meta data objects if you don't need/want them.

    If you want code-generation and submission, we suggest to use the SDKs and/or Powershell.


    Michael Rys

    • Proposed as answer by Michael Amadi Wednesday, January 27, 2016 9:10 PM
    • Marked as answer by Hubix2000 Wednesday, January 27, 2016 9:12 PM
    Wednesday, January 27, 2016 8:51 PM
    Moderator