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

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 61

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.

Erland Sommarskog 138.7K Reputation points MVP Volunteer Moderator

2021-04-12T21:37:45.39+00:00

Hopefully, Andrei's answer gives you something to work from. But else you will need to post the full statements together with the error messages, and without anonymising anything. If we don't see what you are doing, we can only make wild guesses about what you are doing.
James Hackney 61 Reputation points

2021-04-13T16:24:26.05+00:00

I think I am having problems other than code although I am not sure what it is. Thanks for your help!

6 answers

Your answer

Erland Sommarskog 138.7K Reputation points MVP Volunteer Moderator

2021-04-12T21:37:45.39+00:00

Hopefully, Andrei's answer gives you something to work from. But else you will need to post the full statements together with the error messages, and without anonymising anything. If we don't see what you are doing, we can only make wild guesses about what you are doing.
James Hackney 61 Reputation points

2021-04-13T16:24:26.05+00:00

I think I am having problems other than code although I am not sure what it is. Thanks for your help!

Answer 1

AndreiFomitchev 16

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.

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

James Hackney 61 Reputation points

2021-04-13T15:53:14.317+00:00

Both databases are already created and have data in them.

Use <DB Name>.<Schema Name>.<Table Name>

Yes, this is what I did. Both with [ ] and without.

Check that your account has access for SELECT / INSERT in the databases/tables.

I am the creator and database administrator of both databases. I am able to perform select/insert functions in both databases as well as create stored procedures, functions and triggers.

I will add the complete code. Also, this is Azure SQL Database instead of SQL Server if that makes a difference.

Answer 2

MelissaMa-msft 24,246 Moderator

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.

James Hackney 61 Reputation points

2021-04-13T16:22:16.037+00:00

I am the creator of both databases and am a member of the admin group. Both databases have data in them. I can perform select/inserts, create stored procedure, functions and triggers.

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

I tried the above command in all three databases and received NULL in all of them, one of which is been up and running and has been used with no issues for more than a year.

One thing I notice that is different than our on-prem SQL Server dbs though is when I click on the "Available Databases" drop down menu in SSMS, only the master and the db I clicked on in the dropdown menu appears. If I want to access the other dbs, I have to first click master for all of the dbs to show. In our on-prem sql server dbs, all databases on the server appear regardless of which one I am clicked on.

Answer 3

Erland Sommarskog 138.7K MVP Volunteer Moderator

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.

James Hackney 61 Reputation points

2021-04-14T12:26:47.52+00:00

Well that explains that. All of our databases work in conjunction with audit databases so the thought that two databases couldn't interact with each other never occurred to me. Thank you!.

Answer 4

MelissaMa-msft 24,246 Moderator

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.

James Hackney 61 Reputation points

2021-04-14T12:27:35.447+00:00

Thank you for that information! I will check it out.

Answer 5

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

1 deleted comment

Comments have been turned off. Learn more