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

HI @James Hackney ,

Thanks for your update.

Actually I am not familar with Azure SQL Database.

I did some research and found one useful article which introduces some detailed steps of cross-database query in Azure SQL Database.

After creating two tables in different Azure SQL database, you also need to create database scoped master key and credentials ,External data sources and External tables.

Please refer more details from below and check whether it is helpful:
Cross-database Query in Azure SQL Database

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Working Example: CREATE DATABASE testDB1; CREATE DATABASE testDB2; USE testDB1 GO CREATE TABLE TestTable1(id1 Int) GO USE testDB2 GO CREATE TABLE TestTable2(id2 Int); GO INSERT INTO testDB1.dbo.TestTable1(id1) VALUES (1234);

INSERT INTO testDB2.dbo.TestTable2(id2) SELECT id1 FROM testDB1.dbo.TestTable1;

SELECT * FROM testDB2.dbo.TestTable2 -- It returns 1234.

1. Use <DB Name>.<Schema Name>.<Table Name>
2. Check that your account has access for SELECT / INSERT in the databases/tables.

Answer 4

Hi @James Hackney ，

Welcome to Microsoft Q&A!

You could refer Andrei's example and check whether any is different or missing.

Besides, it could be recommended to post the DDL and sample data together with the error message.

You could confirm existance of a table using following query:

USE DATABASE_Name  
SELECT OBJECT_ID('SDCS_AUDIT.BMA_FORM_DATA','U')  

OR

SELECT OBJECT_ID('DATABASE_Name.SDCS_AUDIT.BMA_FORM_DATA','U')  

If output is not NULL then your table already exists.

In addition,regarding the errror message 'invalid object name', you can make sure below points:

• Check you are connectiong to the correct database.
• In SSMS,go to Edit -> IntelliSense ->Refresh Local Cache or right click the database, and click "refresh".
• Check you have all permission on that table including view definition.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Also, this is Azure SQL Database instead of SQL Server if that makes a difference.

Yes, that makes a difference. You can't make cross-database references in Azure SQL DB. In Managed Instance you can, but not DB.