I have set up a link to another database in MSSQL under linked servers (ODBC link called TEST).
I am looking to set up a stored procedure to insert data into a table in this database from data in my current MSSQL database.
I can read data from the database with:-
SELECT * FROM OPENQUERY(TEST, 'select * from Jobs') AS derivedtbl_1
I would like to run:-
Insert into TEST.Jobs (JobNumber, JobDesc) Values (123, 'Test Job')
I am not sure of the correct sytax for the link to the table in the linked server - TEST.Jobs does not work.
Appreciate any ideas.
TEST.dbadb.dbo.Jobs will work -- You need to replace the database name and object owner in this statement .
For more info,
Hope this helps.