MSSQL - Inserting into a table in a linked server

As per Books Online - When you execute a distributed query against a linked server, include a fully qualified, four-part table name for each data source to query. This four-part name should be in the form
linked_server_name.catalog.schema.object_name

for example,
TEST.dbadb.dbo.Jobs will work -- You need to replace the database name and object owner in this statement .
For more info,
http://msdn.microsoft.com/en-us/library/ms188721.aspx
Hope this helps.

>> SELECT * FROM OPENQUERY(TEST, 'select * from Jobs') AS derivedtbl_1

Seems like you already created the Linked server named TEST.
Then you can do it like

Insert into TEST.db_name.schema_name.Jobs (JobNumber, JobDesc)
Values (123, 'Test Job')

Thanks:-

I have tried combinations with this but I cannot get to work. I have also read the msdn info on distributed queries but think I need the catalog and schema names and I'm not sure where to find them.

Sounds like from your comments the database name is the catalog and the schema is the owner. I am not sure of the database name - The ODBC connection is just to a file location. Also not sure of the "Owner" of this database - have passed a login and password - Login "Administrator.

Appreciate any other ideas.
I think the Catalog or schema is MSDASQL as this has got further in the format TEST.MSDASQL.Jobs

What is the target server in your Linked Server ?
Is it also MS SQL Server or any other data sources. also please let me know the provider of the linked server.

Target server is a linked ODBC database - Specialist database for accounting application with thier own ODBC driver. All works OK through DSN connections to database from say MS access or Excel. Trying to set up so I can link to SQL - has been working OK to read but now would like to write some info.

Capture the screenshot of the LinkedServer by opening the catelog in Management studio and in the right hand side the objects list will be there.
and send it to us.

Sorry not sure what you are after - I have a linked server set up under Server Objects > Linked Server - not sure what the Catelog is - I can open up the properties of the linked server but I just have a connection string in there to the database.

OK but nothing much there. The Provider is Microsoft OLE DB Provider for ODBC Drivers if this helps adn I am using MSSQL 2005
SQL.JPG

OK - I have a copy of SQL 2008 management studio that I have been trying out and used this on another PC.

This gives better info on the schema and catalog as I think you were indicating. I have sorted out the 4 word combination. The second was Schema which needed to be in square brackets to work - Only figures this out because I could drill down to the table in 2008 and right click and script as select or insert