INSERT INTO from SELECT Values 1

[Mike555]

I'm trying to use the VFP Command Window to insert data from one table to another. Each time I try this I get a "syntax error" error message. Both tables reside in the same directory. First I use the USE command to specify that I want to use both tables. Then I run this statement:

[b]INSERT INTO[/b] mo_DTL (Md_date, Md_loc_id, Md_mo_seq, Md_amount, Md_emp_no, Md_fee, Md_payee, Md_payor, Md_empname); [b]SELECT[/b] Md_date, Md_loc_id, Md_mo_seq, Md_amount, Md_emp_no, Md_fee, Md_payee, Md_payor, Md_empname [b]FROM[/b] mo_old

Could someone please let me know what I'm doing incorrectly? Thanks!

 

[jimstarr]

Look at the Help file for the INSERT - SQL command. Basically the syntax is as follows:

INSERT INTO dbf_name [(fname1 [, fname2, ...])] ;
VALUES (eExpression1 [, eExpression2, ...])

Jim

 

[craigsboyd]

Mike555,

Assuming that you actually have a carriage return after that semi-colon (if you don't add a space before the semi-colon and a carriage return after it like shown below), I'd say your statement looks viable for Visual FoxPro 8 or 9. If it continues to give you problems, reduce it to the minimum number of parts and build up from there, such as:

INSERT INTO mo_DTL (Md_date) ;
SELECT Md_date FROM mo_old

...then if that works proceed with...

INSERT INTO mo_DTL (Md_date, Md_loc_id) ;
SELECT Md_date, Md_loc_id FROM mo_old

...just keep adding fields until it fails. that should give you some clue as to what is causing the problem, which should then lead to a solution. If you are using an earlier version of Visual FoxPro than 8 then jimstarr's suggestion is the one to go with.

SweetPotato Software Website
My Blog

 

[almcnicoll]

Mike,

are you trying to insert into mo_DTL the values you're selecting from mo_old? IF so, you need to run those two statements into one. I'd be tempted to do it with some VB but if you need to do it with pure SQL you could try:

INSERT INTO mo_DTL (field1,field2,field3,...) VALUES (SELECT field1,field2,field3,... FROM mo_old WHERE condition=true)

or (if it doesn't work) the slightly uglier

INSERT INTO mo_DTL (field1,field2,field3,...) VALUES (SELECT field1 FROM mo_old WHERE cond=true,SELECT field2 FROM mo_old WHERE cond=true,SELECT field3 FROM mo_old WHERE cond=true,...)

I haven't tried either I'm afraid, as time is short...

Good luck,

Al

 

[Mike555]

Thank you all for your suggestions. craigsboyd, I kept adding fields as you suggested until it failed. It turns out that it was failing due to null values being imported into a field that did not allow nulls. So I made a modification to fix that and now it works fine. Thanks, everyone, again for your help!