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

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 137.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 41 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

AndreiFomitchev 16 Reputation points

2021-04-12T21:04:12.433+00:00
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.
Was this answer helpful?
James Hackney 41 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.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the 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

6 answers

Your answer