Is there a simple way to reference another database on the same server (preferably from a MERGE command)? I have two SQL databases in Azure, ‘main-sql-dev’and ‘temp-sql-dev’. I want to update the main database from the temp (transitional database). Is this doable?
I looked at the Elastic Query Feature for SQL Azure but this says it doesn’t allow for inserts. How are you supposed to update from one database to another in Azure?
Basically, if i’m in the ‘temp-sql-dev’ database I need a way to access the ‘main-sql-dev’ database? This used to be a simple case of referencing the database.
E.g.
select top 10 * from [main-sql-dev].[dbo].[ContractDetails];
In short, you can't do that in Azure SQL Database -- with Elastic query or otherwise. As you pointed out:
Elastic query currently only supports read-only access to external tables. You can, however, use full Transact-SQL functionality on the database where the external table is defined. This can be useful to, e.g., persist temporary results using, for example, SELECT <column_list> INTO <local_table>, or to define stored procedures on the elastic query database that refer to external tables.