Problem inserting records 1

[shangrilla]

Tables XXX and YYY have the same structure.

USE XXX
scan
SCATTER MEMVAR
INSERT INTO YYY FROM MEMVAR where xxx.pkey <> yyy.pkey (error in above statement)
endscan

How can I fix this problem?

 

[jimstarr]

The INSERT statement doesn't take a WHERE clause.

Jim

 

[ramani]

HI

USE XXX
SCAN
SCATTER MEMVAR MEMO
INSERT INTO YYY FROM MEMVAR
ENDSCAN



ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[zhavic]

If you can use SELECT statement and not need to SCAN throught all records, try this

* select records from xxx that are not in yyy based on 'pkey' into cursor
SELECT * FROM xxx INTO cursor &quot;XYZ&quot; ;
WHERE xxx.pkey NOT IN ( SELECT pkey FROM yyy )

* open yyy and add selected records from cursor
SELECT 0
USE yyy
APPEND FROM DBF( &quot;XYZ&quot; )
* close cursor
USE IN &quot;XYZ&quot;


Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[shangrilla]

workingdir = &quot;c:\myApp\&quot;
backupdir = &quot;c:\backup\&quot;

Select * FROM workingdir+&quot;&myTab&quot; INTO table &quot;XYZ&quot; ;
WHERE pkey NOT IN ( SELECT pkey FROM backupdir+&quot;&myTab&quot; )

Select 0
if used('backupdir+&quot;&myTab&quot;')
use in backupdir+&quot;&myTab&quot;
endif
use backupdir+&quot;&myTab&quot;
* ERROR ABOVE &quot;FILE IS IN USE&quot;
Append FROM DBF( &quot;XYZ&quot; )
Use IN &quot;XYZ&quot;

Where is the problem?

 

[zhavic]

I thing, there:

if used('backupdir+&quot;&myTab&quot;')

may be
if used( backupdir+&quot;&myTab&quot; )

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[shangrilla]

if used(backupdir+&quot;&myTab&quot
use in backupdir+&quot;&myTab&quot;
endif
use backupdir+&quot;&myTab&quot;

Same problem.

 

[zhavic]

I am sorry, this is it:
'USED()' and 'USE IN' commands uses only alias for table ( without path )

just try something like this:

if used( &quot;&myTab&quot; )
use in &quot;&myTab&quot;
endif
use backupdir+&quot;&myTab&quot;


Zhavic



---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[craigsboyd]

shangrilla,

There are a couple of things I don't understand about your code and what you are trying to do, but let's try and get the syntax right for you first...the following code assumes that your table is named: mytab


workingdir = &quot;c:\myApp\&quot;
backupdir = &quot;c:\backup\&quot;

Select * FROM &workingdir..myTab INTO table &quot;XYZ&quot; ;
WHERE pkey NOT IN ( SELECT pkey FROM &backupdir..myTab )

if used('myTab')
use in 'myTab'
endif

*!* Probably should leave this next line commented out
*!* since it will already be open in a workarea
*!* because you included it in your SQL
*!* It also probably has the alias of &quot;B&quot;
*!*
*!* use (backupdir+ 'myTab.dbf') in 0

Append FROM DBF( &quot;XYZ&quot; )
Use IN &quot;XYZ&quot;


Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]

 

[shangrilla]

Hi Slighthaze:

This is what I am trying to achieve. I am backing up selected data from my existing tables and move it to another tables with the same structure.

myTab = contains a list of all the tables that need to backed up. It loops.

workingdir = &quot;c:\myApp\&quot;
backupdir = &quot;c:\backup\&quot;

Select * FROM &workingdir..myTab INTO table &quot;XYZ&quot; ;
WHERE pkey NOT IN ( SELECT pkey FROM &backupdir..myTab )

above statement gets the records out of the c:\myApp\myTab(workingdir+&quot;myTab&quot and inserts records into XYZ only when they do not already exist in c:\backup\myTab(backupdir+&quot;myTab&quot

Now I need to insert the records from XYZ into c:\backup\myTab(backupdir+&quot;myTab&quot and thats where I am having problem where I get the error message saying c:\backup\myTab is in use.

Help?

 

[zhavic]

The last post, that I posted not working for you ?

Have you any code before that you are posted, that is working with backupdir + &quot;&myTab&quot; ?

Try something like this:
( combination of my post and that the 'Slighthaze' post )


workingdir = &quot;c:\myApp\&quot;
backupdir = &quot;c:\backup\&quot;

* open backup table and set alias to it
USE backupdir+&quot;&myTab&quot; IN 0 ALIAS &quot;Backup_table&quot;

* this select uses just opened table backupdir+&quot;&myTab&quot; in alias &quot;Backup_table&quot;
* and also you can use cursor instead of table ( ofcourse, if you can ), the difference is only, that cursor is temporary table, and Foxpro automaticaly delete it after it is closed, and also this cursor is readonly ( if READWRITE is not specified - VFP7 )

Select * FROM workingdir+&quot;&myTab&quot; INTO cursor &quot;XYZ&quot; ;
WHERE pkey NOT IN ( SELECT pkey FROM &quot;Backup_table&quot; )

* select backup table, that is opened
SELECT &quot;Backup_table&quot;

* insert records
APPEND FROM DBF( &quot;XYZ&quot; )

* close tables
USE IN &quot;XYZ&quot;
USE IN &quot;Backup_table&quot;

------------------------------------
Slighthaze,
*!* It also probably has the alias of &quot;B&quot;

I don't thing so, because if there is opened
tables that apear in select statement, it uses them
For example:
Have table 'Table1'

USE Table1 IN 0 ALIAS 'Some_other_alias'

* this select do not open Table1 again, it just use table that is opened ( 'Some_other_alias' )
SELECT * FROM Table1

( I am using VFP 7.0, may be this is not working with earlier versions of Foxpro, I don't now it )

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[craigsboyd]

Zhavic,

I don't understand the use of &quot;&myTab&quot; in your code posted above. If you wanted the contents of a variable named mytab you could use EVALUATE(mytab), but I doubt the name of the table is &quot;&mytab&quot;, it seems like you are trying to do some kind of macro substitution here, but if it is inside the quotes it will be considered nothing more than a string. Am I missing something here?

I don't thing so, because if there is opened
tables that apear in select statement, it uses them
For example:
Have table 'Table1'

USE Table1 IN 0 ALIAS 'Some_other_alias'

* this select do not open Table1 again, it just use table that is opened ( 'Some_other_alias' )
SELECT * FROM Table1


I completely agree with you given the example you are using, however the case that is proposed here is the other way around...the SQL statement comes FIRST and then the table is being used. Given that the SQL statement will have already opened the table it is unnecessary to Use the table in 0 later on in the code.

There is something else at play here with the message shangrilla is getting regarding the table already being in use when attempting to do the append statement. I am guessing that it is something that we are not privy to and we would need more information about the settings and code executed prior to the code that has been posted. The reason I say this is if the code that I posted is executed as is it will work as expected (given tables existing as specified etc.).

Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]

 

[zhavic]

Slighthaze,

I don't understand the use of &quot;&myTab&quot;

When I see this in second post of 'shangrilla', I don't understand it too ( I am not using it this way )
But I try some example:

CREATE TABLE table1 ( Column1 C(1) )
myTab = &quot;table1&quot;
USE &quot;&myTab&quot;

and it works.


I agree with you, that there may be some code before this, which uses the table 'myTab'.
This is because I put 'USE command' before select.
I think, if the table 'myTab' is opened somewhere before,
then the code I posted raise error on the line with 'USE command' and than 'shangrilla' can assume, that the problem is somewhere before.

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.