SQL Table Inserts

Inserting a row into a table in SQL

The example below is an SQL function in Net.Data showing the simplest form of an add function, inserting into a table using a values list.

SQL Data Insert

%{ ***************************************************** %}
%{ ************ Add record to file ********************* %}
%{ ***************************************************** %}

%FUNCTION(DTW_SQL) AddRecord(IN	pCODE, pDSC, pARA, pTYP ) {
 Insert into $(tableName) 
   (STDSTS,STDDSC,STDARA,STDTYP,STDDTE,STDTIM,STDUSR,STDPGM)
 Values ($(pCODE),'$(pDSC)','$(pARA)','$(pTYP)',$(Sys_Date),$(Trn_Time),
	'$(REMOTE_USER)', 'PNA00047')

        %message{
                        -803: "Record already exists!" : continue
                                0: "Record added." : continue
        %}

 %report {  %row {  %} %}

%}

Checking Existence

Inserts can be performed from a table row INTO another table. Example 1 shows a simple insert INTO a table from antoher table, where the row does NOT EXISTs Example 2 shows an insert based WHERE two key fields are IN one table, and WHERE the keys are NOT IN another table. 


Example 1.
    Exec SQL
        Insert into TEST_DB/PFSPYCMT A
        Select * from PRD_DB_DS/PFSPYCMT B
        WHERE not EXISTS (select * from TEST_DB/PFSPYCMT c WHERE c.spysln = spysln.B);
Example 2.
   Exec SQL
       Insert into WRSCP021 (Select * from OAS_DOCHEAD b
       Where b.DocCode||b.DocNum IN (select c.DocCode||c.DocNum from WRSCP025 c)
       And b.doccode||b.docnum Not In (select d.DocCode||d.docNum from WRSCP021 d)) ;