Using Arcobjects For Table Updates in SDE environments

ArcobjectsTable.cs

        public static void PopulateFeatureToMetadata(ProductMetadataTables metaTable, IFeatureWorkspace tempFWS, IFeatureWorkspace extractWS, string pidTableName)
        {
                using (ComReleaser comReleaser = new ComReleaser())
                {
                    ITable srcFeatureMetadataTable = extractWS.OpenTable(metaTable.Owner + "." + metaTable.Source_Meta_table);
                    ITable pidTable = extractWS.OpenTable(pidTableName);
 
                    ITable outFeatMetaDataTable = tempFWS.OpenTable(metaTable.Source_Meta_table);
                    //using these to get the field index for the cursor to search on.
                    int tempIndePermId = outFeatMetaDataTable.FindField(metaTable.Meta_field);
                    int tempIndexMetaId = outFeatMetaDataTable.FindField(metaTable.Join_field);
 
                    //Out Cursor/Rowbuffer
                    ICursor outFeatMetaDataCursor = outFeatMetaDataTable.Insert(true);
 
 
                    IRowBuffer outFeat2MDataBuffer = outFeatMetaDataTable.CreateRowBuffer();
                    int indexPermId = srcFeatureMetadataTable.FindField(metaTable.Meta_field);
                    int indexMetaId = srcFeatureMetadataTable.FindField( metaTable.Join_field);
 
                    //perform the join on the relQueryTable
                    IQueryFilter2 pFilter = new QueryFilterClass();
                    //using the subquery as commented out below for inner join on Permanent_Identifier
                    //1:M relationship
                    pFilter.WhereClause = metaTable.Owner + "." + metaTable.Source_Meta_table + "." + metaTable.Meta_field +
                        " in (SELECT " + pidTableName + "." + metaTable.Meta_field + " FROM " + pidTableName +
                        " INNER JOIN " + metaTable.Owner + "." + metaTable.Source_Meta_table +
                        " ON " + pidTableName + "." + metaTable.Meta_field + " = " + metaTable.Owner + "." + metaTable.Source_Meta_table + "." + metaTable.Meta_field + ")";
 
                    //select COUNT(*) from NHD.NHDFeatureToMetadata  where NHD.NHDFeatureToMetadata.PERMANENT_IDENTIFIER in 
                    //(SELECT spg19387_pid.PERMANENT_IDENTIFIER FROM spg19387_pid INNER JOIN  NHD.NHDFeatureToMetadata
                    //ON  spg19387_pid.PERMANENT_IDENTIFIER = NHD.NHDFeatureToMetadata.PERMANENT_IDENTIFIER);
                    ICursor cursor = srcFeatureMetadataTable.Search(pFilter, false);
 
                    IRow row = cursor.NextRow();
                    while (row != null)
                    {
                        outFeat2MDataBuffer.set_Value(tempIndePermId, row.get_Value(indexPermId));
                        outFeat2MDataBuffer.set_Value(tempIndexMetaId, row.get_Value(indexMetaId));
                        outFeatMetaDataCursor.InsertRow(outFeat2MDataBuffer);
                        row = cursor.NextRow();
                        outFeatMetaDataCursor.Flush();
                    }
 
                    //Let comReleaser deal with GC
                    comReleaser.ManageLifetime(pFilter);
                    comReleaser.ManageLifetime(outFeatMetaDataCursor);
                    comReleaser.ManageLifetime(cursor);
                }    
        }

Using a joined selection to get many to one relationship using subquery.
SELECT COUNT(*) FROM NHD.NHDFeatureToMetadata FTM WHERE FTM.PERMANENT_IDENTIFIER IN (SELECT spg19387_pid.PERMANENT_IDENTIFIER PID FROM PID INNER JOIN FTM ON PID.PERMANENT_IDENTIFIER = FTM.PERMANENT_IDENTIFIER);