update/insert one DB to another using openquery

[oakpark5]

OK, so i've linked together a foxpro db and sql server. I can update the foxpro db from EM, and I even created a stored procedure for to update the foxpro table. My next step is to transfer data from the foxpro table to a sql table using either update or insert, but i have to use the openquery function. I'm just kinda stuck on how to write the update from one table to another using openquery. I would like to make this a stored procedure as well.

Software Engineer
Team Lead
damn bill gates, he has everything

 

[NoCoolHandle]

The insert would be a case of insert with a select where the select is your linked server..

E.G.

Insert into sometable (col1, col3,col5,col2)
select colx,coly,colb,colacg from foxproserver...sometable

Not to sure about if a foxpro linked server needs the database argument you should know the sytax for that.

Rob

 

[oakpark5]

AH thanks for the all the help, your coding was right! All it needs is the OPENQUERY to call the other db. But it works so thanks. This is the code i used...

CREATE PROCEDURE PKFOXTOSQL
AS
INSERT INTO testsql (password)
select * from OPENQUERY(PK, 'select password from test')
GO


Software Engineer
Team Lead
damn bill gates, he has everything