Insert data from a query into a table 2

[RedLion]

I don't know if it's possible, but if it is, I would like to know what the correct syntax is?

I would like to select data from table1 and insert it into table2.

e.g.

insert into table2 (select id, name, address from table2)

I know it is possible when I use an array:
e.g.
select id, name, address from table2 into array temp

insert into table2 from array temp


Second question:
VFP has the command COPY STRUCTURE TO which copies the structure from table1 to table2. My question is: Can the structure from a table be copied to a cursor (create cursor), and if, how can it be done?


Thanks,

Charl

 

[Mike Lewis]

Hi Charl,

If you only want to copy the data from one record at a time, one way to do it would be as follows:

First, go the record in question in table 1. Then:

SELECT Table1
SCATTER MEMVAR
SELECT Table2
APPEND BLANK
GATHER FROM MEMVAR

This assumes that the field names are the same in both tables.

Re your second question.

No, you can't use COPY STRUCTURE to create a cursor. Instead, do this:

SELECT * FROM Table1 WHERE .F. INTO CURSOR Cursor1

Give it a try, and report back if any problems.

Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[RedLion]

Thanks Mike for your quick response!

First, I would like to copy all selected records from table1 to table2

Second, I use VFP6 (I know VFP 7 it is possible with readwrite) and as far as I know (I can be wrong) a cursor from a selection is readOnly, so I can't insert any data into the cursor.


Charl

 

[Mike Lewis]

Charl,

<< I would like to copy all selected records from table1 to table2 >>

OK, that's a little more difficult, but the same principle. You could write some code that SCANs the source table, using a FOR clause to specify the selection condition. Within the loop, use my SCATTER / GATHER code.

Or, use SQL SELECT to put the selected records into a cursor, then APPEND them from the cursor to the target table, like so:

SELECT * FROM Table1 WHERE <your condition> INTO CURSOR MyCursor

SELECT Table2
APPEND FROM DBF(MyCursor)

<< I use VFP6 (I know VFP 7 it is possible with readwrite) and as far as I know (I can be wrong) a cursor from a selection is readOnly, so I can't insert any data into the cursor >>

Yes and No. The code I showed you will create the cursor, in all versions of VFP. But, in VFP 6 and earlier, once you have created the cursor, it is read-only. In other words, you can always create it, but you can't always modify it after it has been created.


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[RedLion]

Thanks Mike,

I knew I had read somewhere that it was possible to use the INSERT statement with the SELECT statement, and the syntax for it is:


INSERT INTO dbf_name [ (fname1 [, fname2, ...] ) ]
SELECT [ (fname1 [, fname2, ...] ) ] FROM tablename WHERE condition


The only thing I had forgotten was that this is only possible in VFP8 (bèta)..... :-(


Secondly, thanks for the nice function append from dbf() I didn't knew this statement. I have to say it works nice! (the use of an array also, but when working with cursors, I don't have to convert anymore to arrays)

Thanks again,

Charl

 

[Mike Lewis]

Charl,

<< I knew I had read somewhere that it was possible to use the INSERT statement with the SELECT statement,>>

That syntax is availalbe in SQL Server, but not in VFP, as far as I know.

Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[RedLion]

Hi Mike,

Copy&Pasted from the VFP 8 bèta documentation:

In the Visual FoxPro Documentation
What's New in Visual FoxPro 8.0
Describes the new features and enhancements included in this version of Visual FoxPro.

Data Features Enhancements
Describes additions and improvements to Visual FoxPro data features.

Automatically Incrementing Field Values
You can now increment field values automatically for both DBC tables and free tables using the new autoincrementing feature in Visual FoxPro. You can use the AUTOINC clause in the CREATE TABLE SQL and ALTER TABLE SQL commands to specify values for NEXTVALUE and STEP. You can also select the Integer (AutoInc) data type for a field and set values for the Next Value and Step fields in the Fields tab in the Table Designer. For more information, see CREATE TABLE - SQL Command, ALTER TABLE - SQL Command, Fields Tab, Table Designer, Autoincrementing Field Values in Tables, and Setting Autoincrementing Field Values.

The AFIELDS() function now returns 18 columns instead of 16 due to new support for autoincrementing field values. Column numbers 17 and 18 now specify the NextValue and Step fields, which contain values of Numeric (integer) type. For more information, see AFIELDS( ) Function.

The COPY STRUCTURE EXTENDED command adds the numeric FIELD_NEXT and FIELD_STEP fields to support autoincrementing. For more information, see COPY STRUCTURE EXTENDED Command.

Visual FoxPro modifies the table header when you turn on or add autoincrementing for field values. For more information, see Table File Structure.

Support for Collating Sequences
In previous versions, you needed to use the SET COLLATE TO command to specify a collating sequence other than the default when creating an index or index tag. You can now specify a different collating sequence when you create or modify a table via the Indexes tab in the Table Designer and when you use the COLLATE clause and its parameter with the CREATE TABLE SQL, ALTER TABLE SQL, and INDEX commands. For more information, see Indexes Tab, Table Designer, CREATE TABLE - SQL Command, ALTER TABLE - SQL Command, and INDEX Command.

Implicit Data Conversion for SELECT...UNION SQL Command
The SELECT SQL command with the UNION option now performs implicit data conversion for data types that support implicit conversions. For example, UNION operations with columns of different lengths or that contained different types previously failed unless you explicitly matched the column sizes using a conversion function. Suppose you have the following code:

CREATE CURSOR t1 (f1 c(10))
INSERT INTO t1 VALUES ('1111111111')
CREATE CURSOR t2 (f1 c(2))
INSERT INTO t2 VALUES ('11')
SELECT * FROM t1 INTO CURSOR t3 UNION SELECT * FROM t2 && Fails.
This code failed unless you explicitly match the column sizes as shown:

SELECT * FROM t1 INTO CURSOR t3 UNION SELECT SUBSTR(f1,1,2) FROM t2
You no longer need to specify or use explicit conversion functions for data types that support implicit data conversion. For more information about data type conversion and precedence, see Data Type Conversion and Precedence in SELECT - SQL Command.

Inserting Rows from a SELECT SQL Command
You can now insert rows from a SELECT SQL command when you use the INSERT SQL command with the following syntax:

INSERT INTO dbf_name [ (fname1 [, fname2, ...] ) ]
SELECT [ (fname1 [, fname2, ...] ) ] FROM tablename WHERE condition
You can copy the contents of selected columns or all columns in a row. However, you must make sure the data you copy is compatible with the columns in the rows that you copy to. Visual FoxPro updates the _TALLY system variable with the number of rows inserted.

See Also
What's New in Visual FoxPro 8.0 | Interactive Development Environment (IDE) Enhancements | Language Enhancements | Miscellaneous Enhancements | Behavior Changes Since Visual FoxPro 7.0