Accessing tables across databases?

  • Hi,

    As far as I am aware T-SQL stored procedures can only access tables in the database where the stored procedure is running, and it is not possible to create views that span databases. The requirement I have is to combine data from a table in two different databases into another table in another database.

    Do I need to create a worker role with a queuing mechanism then instruct that role to open a connection to each of the three databases, so read the 1st table data via 1st connection, read 2nd table via 2nd connection and write to the 3rd table via the 3rd connection?

    What other techniques are there for accessing tables across databases in Azure?

    Thanks,

    Paul.

  • Cross db queries are not supported in azure , searches on google gave a solution that query this tables individually and join it in the application itself.

  • If you need to access data on more than one database within Microsoft Azure SQL Database, you must do that at that application level. The databases are absolutely isolated from each other. The reason for this is, while you define a server for your databases, it's only a virtual construct. The actual location of the databases maybe be on any number of different physical boxes within your data center. There's no way for them to define linked servers for these, especially because they'll be constantly moving anyway as failovers occur. That's not even to mention the nature of cross-server queries and their tendency to default to scans of all data. There's just no way they can build a shared infrastructure and support such a mechanism.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply