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

Question

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.

Answer 1

-- For Azure:
CREATE MASTER KEY; -- create master key
GO

CREATE DATABASE SCOPED CREDENTIAL CrossDbCred
WITH IDENTITY = 'CrossDb', -- login user name
SECRET = 'P@ssword'; -- password
GO

CREATE EXTERNAL DATA SOURCE table2
WITH
(
TYPE=RDBMS,
LOCATION='server.database.windows.net',
DATABASE_NAME='database2',
CREDENTIAL=CrossDbCred
);
GO

try also another LOCATION format:
LOCATION = 'sqlserver://WINSQL2019'
See here: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver15&tabs=dedicated

-- in database1
CREATE EXTERNAL TABLE [dbo].[table2]
( -- in database1.table2 the same columns as in database2.table2
colimn1 INT,
...
)
WITH (DATA_SOURCE = [database2],
SCHEMA_NAME = 'dbo',
OBJECT_NAME = 'table2'
);
GO

Answer 2

@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'
;