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 (.....)
INSERT INTO [SDCS_AUDIT].[BMA_FORM_DATA] (...)
as well as this:
INSERT INTO DATABASE_Name.SDCS_AUDIT.BMA_FORM_DATA (...)
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.