%InsertSelect

%InsertSelect([DISTINCT, ]insert_recname, select_recname [ correlation_id][,
select_recname_n [ correlation_id_n]] [, override_field = value]. . .)

I was trying to understand how %insertselect could be used. I have to admit I got stumped when I looked at the syntax and the examples in Peoplebooks . At least until, I saw it used in an app engine program and found.

If you are not clear on how is is used, I suggest you open up an app engine program which does use that syntax and follow the steps:

1. Right click the sql action and view sql.
2. right click on the sql text and select resolve metasql from the popup menu and behold you will see the resolved sytax.
3. Open the target and the source record , examine the table structures and review the sql in light of this information.

Generally, if the fields in the target table and the source table are the same and you are moving data from the source table to the target table and the key structure in the two tables is the same, the simple syntax can be used with no overide fields and no DISTINCT .

Example 1
=======

TgtTbl: col1 (key), col2(key) col3
SourceTbl: col1 (key), col2(key) col3

%INSERTSELECT( TgtTbl, SourceTbl)
FROM SourceTbl
WHERE col1 = 'A'

This will evaluate to

INSERT INTO tgtTble ( col1, col2,col3) <-- build from the recfields of the tgttable.
SELECT col1, col2,col3 <-- built from the sourcetbl rec fields that match the recfield on the target.
FROM SourceTbl WHERE col1 = 'A'

It is clear that the insert part is build on the basis of the fields in the target table. The select part is build on the basis of the fields in the source table .The field names in the target table should exist on the source table.

However, if you want to assign values to fields in the target table, that should not come from the source table or do not exist in the source table or maybe the field name that do not match, you would provide an override_field = value.

EXAMPLE 2 =========

%INSERTSELECT( TgtTbl, SourceTbl, COL2 = 5 )
FROM SourceTbl WHERE col1 = 'A' In this case, the insert select will be build such that 5 will be inserted in COL2 field for all the rows inserted in the target table. All other fields that exist in target table will get the values from the correspomnding fields in the source table.

This will be resolved to

INSERT INTO tgtTble ( col1, col2,col3)
SELECT col1, 5,col3 <-- built from the sourcetbl rec fields that match the recfield on the target in comination with the override value provided .
FROM SourceTbl WHERE col1 = 'A'

EXAMPLE 3
=========

%INSERTSELECT( TgtTbl, SourceTbl, COL2 = COL10 )
FROM SourceTbl WHERE col1 = 'A'

The fields in the TgtTbl are COL1, COL2 and COL3. Those in the target table are COL1,COL3,COL10.
It will resolve to the following:
INSERT INTO tgtTble ( col1, col2,col3)
SELECT col1, col10,col3
FROM SourceTbl
WHERE col1 = 'A'



When to use Distinct
==============

If the keys in the target table will not allow insert of all rows returned from the target table ( one resaon could be will cause unique constraint violation if rows in source table are not unique for the target table based on the target table's key ).

Consider the following structure:

tgtTble: col1 key, col2 key
Sourcetbl:col1 key, col2 key, col3 key

%INSERTSELECT( DISTINCT,TgtTbl, SourceTbl)
FROM SourceTbl

will resolve to

INSERT INTO TgtTbl(col1,col2)
SELECT DISTINCT col1,col2
FROM sourcetbl

Had the word DISTINCT not been used, multiple rows with the same key would have been attempted to be inserted causing a unique constraint.


Hope the above throws some light on how it works.