copy table content using select * into

[makdu]

i want to delete a record from employee table with a specific empid and before deleting , i want to copy all the equivalent data in employee table to another table, deletemprcd.

I tried using

SELECT  * into  deletemprcd  from employee where  empid =lclempno

but getting syntax error
Here i want to delete a record from employee table with a specific empid and before deleting , i want to copy all the equivalent data in employee table to another table, deletemprcd.
I also gave a try with

insert into  dltemprcd   select * from employee  where  empid =lclempno

but the result is syntax error
What is wrong with the code

 

[Olaf Doschke]

Your second SQL, the insert into, should work, if you are using VFP9.

This exact insert into compiles for me without syntax error. So are you using VFP9?

Bye, Olaf.

 

[markros]

Your first syntax is T-SQL syntax and it doesn't work for VFP.

Your second syntax should work provided you have dltemprcd table with the same fields as employee table (or you can have one extra first field which is AutoInc in dlTempRcd table).

 

[bborissov]

The INTO should have a direction.
INTO what? array, cursor or table?

SELECT * FROM from employee where  empid =lclempno INTO TABLE deletemprcd

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[makdu]

I am using VFP6

 

[makdu]

My dltemprcd table is a copy of the employee table. ( all the fields are same)
Suggested by bborissov, do we need two From from in a select statement. Is this wat is intended

SELECT * FROM employee where  empid =lclempno INTO TABLE dltemprcd

This is not updating the record to dltemprcd table

 

[alvechurchdata]

You're right, there should only be the one "from".

Later versions of VFP can be very helpful in guessing the next keyword and if you're typing fast you don't always notice that Fox has already given you the word you've just typed.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Olaf Doschke]

INTO TABLE is not good, it will recreate the table all the time. You already have that destination table.

IN VFP6 do this:

SELECT employee
LOCATE FOR EmpID = lcempno
IF FOUND()
   SCATTER MEMO NAME loRecord
   SELECT dltemprcd
   APPEND BLANK
   GATHER MEMO NAME loRecord
ENDIF

Bye, Olaf.

 

[Olaf Doschke]

By the way, you are not limited to 8 char table names, you can use a more speaking name for dltemprcd, eg downloadtemprecord.dbf or deletetemprecord.dbf or delete_MPR_CD.dbf or what ever it should mean.

Another way to let users edit different employees and easily naviaget between them: Put a listbox on the left side, and bind the employee table to it, to show the employee ID. then only do thisform.refresh() in interactivechange of the listbox and you already have a navigation. Users can simply scroll through the IDs and pick one, and they can also make use of incremental search, eg activate the listbox and type in the beginning of the ID to move through the list.

Bye, Olaf.

 

[friend01]

The proper syntax to a SELECT is as follows:

SELECT * FROM yourdbf WHERE yourcondition into table resultname

example:
SELECT * from employee WHERE empid=lclempno INTO TABLE dltemprcd


Thanks,
F1

 

[Imaginecorp]

Try:

use employee in 0 exclu
lclempno = employee.empid (THE ID YOU want to delete)

insert into dltemprcd ;
select * from employee ;
where not deleted("employee") and ;
employee.empid = lclempno

delete for employee.empid = lclempno ;
in employee
Pack

 

[makdu]

I will try these solutions