insert_recordset DestinationTbl select SourceTbl // where SourceTbl.Feild1 <= 100
// If you have any condition to filter else al the record will be transfer
/*****************************************************/
If you want to map the fields you need to.
insert_recordset DestinationTbl (Feild1, Feild2, Feild3...)select Feild1, Feild2, Feild3... from SourceTbl where SourceTbl.Feild1 <= 100; //if conditionRegards,
| SELECT - extracts data from a database |
| UPDATE - updates data in a database |
| DELETE - deletes data from a database |
| INSERT INTO - inserts new data into a database |
| CREATE DATABASE - creates a new database |
| ALTER DATABASE - modifies a database |
| CREATE TABLE - creates a new table |
| ALTER TABLE - modifies a table |
| DROP TABLE - deletes a table |
| CREATE INDEX - creates an index (search key) |
| DROP INDEX - deletes an index |
| Student -------------------------------------- |
|||
| studentID | Name | Gender | Native |
| Stu001 | Fathima | Female | IND |
| Stu002 | Faridth | Male | IND |
| Stu003 | Faheed | Male | KSA |
| Stu004 | Fakrudeen | Male | KSA |
| Stu005 | Firdose | Female | IND |
| Stu006 | Fahima | Female | KSA |
| SQL Tutorial | ||
| SQL Select | Basic select concern record | while SELECT field_Name FROM table_name |
| SQL Distinct | List unique records | while SELECT DISTINCT field_Name FROM table_name |
| SQL Where | Where condition | while SELECT field_Name FROM table_name where Table_Name.Field_Name ==/=< conditions |
| SQL And & Or | Show only IND female student | while SELECT StudentID FROM Student where Student.Gender == Gender::Female && Student.Native == "IND" |
| Show all female record, IND native also | while SELECT StudentID FROM Student where Student.Gender == Gender::Female || Student.Native == "IND" | |
| SQL Order By | Sort the record | while SELECT DISTINCT field_Name FROM table_name ORDER BY table_name.field_Name ASC/DESC |
| SQL Insert Into | MyTable MyTable; ttsBegin; select MyTable ; MyTable.AccountNum = '1101'; MyTable.Name = 'MyName'; MyTable.insert(); ttsCommit; | |
| SQL Update | Student Student; ttsBegin; select forUpdate Student where Student.StudentID= "Stu0002"; Student.StudentID = 'Stu0009'; Student.Name = "Rahima"; Student.update(); ttsCommit; | |
| SQL Delete | static void DeleteMultiRow1bJob(Args _args) | |
| { | ||
| MyWidgetTable tabWidget; // extends xRecord. | ||
| ; | ||
| ttsBegin; | ||
| while select | ||
| forUpdate | ||
| tabWidget | ||
| where tabWidget .quantity <= 100 | ||
| { | ||
| tabWidget .delete(); | ||
| } | ||
| ttsCommit; | ||
| } | ||
| SQL Select Top | Code fastup the search and once found it will search for remaining data | select FirstOnly PersonnelNunber where HcmWorker.PersonnelNumber == "0000567"; |
| SQL Between | There is no between in X++ but | while select Table_Name where Table_Name.Field_Name >= RangeFromValue && Table_Name.Field_Name <= RangeToValue |
| SQL Wildcards | while SELECT DISTINCT field_Name FROM table_name | |
| SQL Not Null | while select Table_Name where Table_Name.Field_Name != Null |
| Table Name | Table Description |
| EcoResProduct | The EcoResProduct table stores products and is the base table in the products hierarchy. |
| EcoResProductMaster | The EcoResProductMaster table stores product masters. |
| EcoResProductIdentifier | The EcoResProductIdentifier table contains a product identification that is available for users. |
| EcoResDistinctProduct | The EcoResDistinctProduct table stores products. |
| EcoResDistinctProductVariant | The EcoResDistinctProductVariant table stores product variants. |
| EcoResProductDimensionGroup | The EcoResProductDimensionGroup table contains information about a dimension group. |
| EcoResProductDimensionGroupProduct | The EcoResProductDimensionGroupProduct table stores information about relationships between products and dimension groups. |
| EcoResColor | The EcoResColor table stores color names. |
| EcoResSize | The EcoResSize table stores size names. |
| EcoResConfiguration | The EcoResConfiguration table stores configuration names. |
| EcoResProductMasterColor | The EcoResProductMasterColor table stores information about colors assigned to product masters. |
| EcoResProductMasterSize | The EcoResProductMasterSize table stores information about sizes that are assigned to product masters. |
| EcoResProductMasterConfiguration | The EcoResProductMasterConfiguration table stores information about configurations assigned to product masters. |
| EcoResProductVariantColor | The EcoResProductVariantColor table stores information about the colors that are assigned to product variants. |
| EcoResProductVariantSize | The EcoResProductVariantSize table stores information about the sizes that are assigned to product variants. |
| EcoResProductVariantConfiguration | The EcoResProductVariantConfiguration table stores information about the configurations that are assigned to product variants. |
| EcoResProductMasterDimensionValue | The EcoResProductMasterDimensionValue table is the base table in the product model dimension hierarchy. |
| EcoResProductVariantDimensionValue | The EcoResProductVariantDimensionValue table is the base table in the product variant dimension hierarchy. |
| EcoResProductDimensionAttribute | The EcoResProductDimensionAttribute table contains definitions of product dimension attributes (categories). |
| EcoResInstanceValue | The EcoResInstanceValue table contains the definitions of the instances of the components or products. |
| EcoResProductInstanceValue | The EcoResProductInstanceValue table contains definitions of values for the instances of attributes of a product. |
| InventTable | The InventTable table contains information about items. |
| InventTableModule | The InventTableModule table contains information about purchase, sales, and inventory specific settings for items. |
| InventItemLocation | The InventItemLocation table contains information about items and the related warehouse and counting settings. The settings can be made specific based on the items configuration and vary from warehouse to warehouse. |
| InventItemSalesSetup | The InventItemSalesSetup table contains the default settings for items, such as site and warehouse. The values are related to sales settings. |
| InventItemInventSetup | The InventItemInventSetup table contains the default settings for items, such as site and warehouse. The values are related to inventory settings. |
| InventItemPurchSetup | The InventItemPurchSetup table contains the default settings for items, such as site and warehouse. The values are related to purchase settings. |
| InventItemSetupSupplyType | The InventItemSetupSupplyType table contains information about the sourcing of items. |
| InventDim | The InventDim table contains values for inventory dimensions. |
| InventDimCombination | The InventDimCombination table contains variants of items. The variants are created as product variants that are based on product dimensions such as size, color, and configuration. These variants are replicated to the legal entity. |
