SQL Select-Insert 1

[Shanachie]

Another one of those "there's got to be an elegant way of doing this" problems.

I want to SELECT some records from a table and INSERT them into another table. I could do it with SCAN as follows:

select table1
scan for field = rightvalue
insert into table2 (field1, ;
field2, ;
field3) ;
values (table1.fielda, ;
table1.fieldb, ;
table1.fieldc)
endscan

This just somehow doesn't seem smooth. Is there a better way? Can I somehow SELECT and INSERT in one statement?

TIA,
Shanachie

 

[RedLion]

Hi Shanachie,

You should place the values from table1 first into variables before you can insert them into another table.

e.q.
select table1
scan for field = rightvalue
cFielda = fielda
cFieldb = fieldb
cFieldc = fieldc
insert into table2 (field1, ;
field2, ;
field3) ;
values (cFielda, ;
cFieldb, ;
cFieldc)
endscan


Also look in this thread for other possibilities
thread184-452994


Charl

 

[ramani]

Hi shanachi,

If the field names are same.. then...
SELECT table1
APPEND FROM tabe2 FOR field = rightValue

OR

SELECT fieldA as field1, fieldb as Field2 ;
FROM table2 WHERE field = rightValue ;
INTO CURSOR myCursor
SELECT TABLE1
APPEND FROM DBF(myCursor)

But your SCAN ... ENDSCAN loop is fine and nothing wrong.
ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[Shanachie]

Thanks, that thread answered the question: I can't do it, at least not in VFP6.0. Ah, well,...

BTW, the code example I used with the references within the Insert to values such as "table2.fielda" actually work just fine. Assigning to memory variables is unnecessary as far as I can tell.

Thanks again,
Shanachie

 

[DSummZZZ]

Or:

select table1
scan for field = rightvalue
scatter memvar
insert into table2 from memvar
endscan
Dave S.