none
INSERT From VFP-Cursor To SQL-TABLE ?

    Question

  • Iam using the following for Sql-Table insert from VFP-Cursor. 
     

    cSQL1="select * from emp_master"
    SQLEXEC(nHandle,cSQL1,'CursD')
    cSQL2="Insert into emp_master (emp_id,fir_name,passport_no) Values (?ep_id,?fr_name,?pass_p)"

    SELECT johu1
    SCAN
    STORE SPACE
    (10) TO ep1
    ep1=
    RTRIM(ep_id)
    IF ep1#SPACE(10)
    SELECT CursD
    LOCATE FOR RTRIM(emp_id)=ep1
    IF !FOUND()
    SELECT johu1
    SQLEXEC(nHandle,cSQL2)
    ENDIF
    SELECT
    johu1
    ENDIF
    ENDSCAN
    SQLDISCONNECT
    (nHandle)

    ===============================================
    But from SQL Server 2005 I checked the Insert can be used like the following

    insert into
    emp_master (emp_id,fir_name,passport_no) select
    RTRIM
    (johu1.ep_id),johu1.fr_name,johu1.pass_p from johu1 where not exists
    (select 1 from emp_master where RTRIM(emp_id)=RTRIM(johu1.ep_id))


    So how to use it from our VFP9?

    Thanks & Regards




     


    U.PARANTHAMAN
    Thursday, March 05, 2009 10:08 AM

Answers

  • YOu can only use "INSERT INTO...SELECT FROM.... "syntax when the source table is directly available to the server (it works in VFP too you know). The problem is that the VFP cursor is not accessible from SQL Server because the cursor is local to the host machine and is not visible outside of that machine by anything else.

    You could use it if you were using VFP Tablesd, have a DBC, and create a linked server from SQL Server to the FoxPro Database.  Then you could use syntax like this:

      INSERT INTO SqlTable (columns) SELECT columns FROM vfpdatabase.vfptable

    but with cursors generated from SQL this will not work
    -- Andy Kramek
    • Marked as answer by Paramu Thursday, March 05, 2009 11:13 AM
    Thursday, March 05, 2009 10:46 AM

All replies

  • YOu can only use "INSERT INTO...SELECT FROM.... "syntax when the source table is directly available to the server (it works in VFP too you know). The problem is that the VFP cursor is not accessible from SQL Server because the cursor is local to the host machine and is not visible outside of that machine by anything else.

    You could use it if you were using VFP Tablesd, have a DBC, and create a linked server from SQL Server to the FoxPro Database.  Then you could use syntax like this:

      INSERT INTO SqlTable (columns) SELECT columns FROM vfpdatabase.vfptable

    but with cursors generated from SQL this will not work
    -- Andy Kramek
    • Marked as answer by Paramu Thursday, March 05, 2009 11:13 AM
    Thursday, March 05, 2009 10:46 AM
  • Thanks a Lot ....I got clear
    U.PARANTHAMAN
    Thursday, March 05, 2009 11:14 AM