Invalid Object Name when attempting to insert data from a table in one DB to a table in another DB on the same server

James Hackney 41 Reputation points
2021-04-12T19:38:47.667+00:00

I am attempting to perform an insert from a table in one database to a table in another database on the same server I have tried this:

INSERT INTO SDCS_AUDIT.BMA_FORM_DATA (.....)

and this:

INSERT INTO [SDCS_AUDIT].[BMA_FORM_DATA] (...)

as well as this:

INSERT INTO DATABASE_Name.SDCS_AUDIT.BMA_FORM_DATA (...)

and this:

INSERT INTO [DATABASE_Name].[SDCS_AUDIT].[BMA_FORM_DATA] (...)

I have also attempted to add the server name in front of the database name but it doesn't like that either.

I know that you need to use sp_linkedserver to create a linked server if the dbs are on two separate servers but from my understanding I shouldn't have to do that if they are on the same server.

Intellisense doesn't show any of the other databases on this server. Is there an option I have to turn on/off somewhere or am I making a bonehead mistake somewhere? This is inside of a trigger by the way, in case that matters.

Azure SQL Database
Developer technologies | Transact-SQL
Developer technologies | Transact-SQL

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.


6 answers

Sort by: Most helpful
  1. ApplebyNick-0083 1 Reputation point
    2021-04-15T15:08:29.117+00:00

    @MelissaMa-msft -- I have just stumbled across this and am having the same issue:
    Now from my understanding, the solution is to use External data source & External Tables as the doc you direct to states however, is it possibly to do this with an Azure Managed identity rather than SQL Auth?

    As Per the original post, both the db's in question are within the same subscription and resource group on the same Server instance, both are serverless.

    This is my thinking so far:
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<xxxxxxxxxxxx>';
    CREATE DATABASE SCOPED CREDENTIAL <credentialName>
    WITH IDENTITY = '<managedIdentityName'
    ;

    Was this answer helpful?


Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.