Search code examples
sqlinsertfoxpro

Foxpro/SQL copy values from cursor to table where id's match

I have a Excel document with a ID column and a Memo type column. (I've made a cursor named Sheet01) I also have a table named Caption with a ID column, Memo type column and a Code (char).

I need to insert the values of the Memo column from document into the table and assign a existent column a value of my choice (in this case 'DE').

The ID column in the cursor Sheet01 has all numers like this: 28.00000000 (from 1 to ~ 1200)
While the Caption table has like this: 28

This is my best try but obviously it does not work. Help!

INSERT INTO captions2(captionid, caption, code) 
SELECT captionid, <caption from the colum 'Header'>, 'DE' FROM Sheet01 WHERE c2.Captionid = Sheet01.Captionid

Sample picture:

http://i.imgur.com/4ugkR7v.png

How do I get the data from excel to cursor? I have a function for that but is similar to APPEND FROM or any other way.

Solution

  • Your SELECT statement isn't correct. Try joining the tables like below.

    INSERT INTO captions2(captionid, caption, code) 
SELECT  s.captionid, c.caption, "DE"
FROM sheet01 s INNER JOIN captions2 c
    ON s.captionid = c.captionid