2

I have to perform cross DB querying in SQL Server 2019 as well as Azure SQL. I am able to create External data source and external tables in both databases, however the syntax seem to be different. Can someone please guide me if there is one standard script that can work in both cases?

Azure SQL Database

  • External Data Source

    CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    TYPE          = RDBMS,
    LOCATION      = 'sourcesqlserver.database.windows.net',
    DATABASE_NAME = 'sourcedb',                    
    CREDENTIAL    = SQLServerCredentials
);

  • External Table

    CREATE EXTERNAL TABLE dbo.SourceTable (
    Col1 int NULL,
    Col2 int NULL
)
WITH (
    DATA_SOURCE = SQLServerInstance2
);

SQL Server 2019

  • External Data Source

    CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION   = 'sqlserver://WINSQL2019:58137' ,
    CREDENTIAL = SQLServerCredentials
);

  • External Table

    CREATE EXTERNAL TABLE [dbo].[SourceTable] (
    Col1 int NULL,
    Col2 int NULL
)
WITH (
    DATA_SOURCE = SQLServerInstance2,
    LOCATION    = N'[SourceDB].[dbo].[SourceTable]'
);
Dai
  • 141,631
  • 28
  • 261
  • 374
Shubham Garg
  • 21
  • 1
  • Hi Shubham Carg, If my answer is helpful for you, you can mark it as answer( click on the check mark beside the answer to toggle it from greyed out to filled in.). This can be beneficial to other community members. Thank you. – Leon Yue Apr 10 '20 at 07:29

1 Answers1

1

Azure SQL SQL database is a cloud database. Azure SQL Database is a fully managed Platform as a Service (PaaS) Database Engine

Even it almost has the same feature with SQL Server, there are still many difference.

They are running in different platform, that's why the syntax could be difference.

Please reference this document to get Transact-SQL differences between Azure SQL Database and SQL Server.

Across database query in Azure SQL database is more complicated than SQL Server. If we want to achieve the same feature with SQL Server in Azure SQL, there are be a syntax difference.

Hope this helps.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23