Perform DML operations using Visual Studio Code

You can add data, modify existing data and query data from tables usingVisual Studio Code plugin.

Insert Data

• Locate the Table Explorer, and click the Refresh Schema to reload the schema.
• Right click on the table where a row needs to be inserted. Choose Insert Row.
• In the Insert Row panel, enter the details for inserting a new row. You can INSERT a new ROW in two modes:
  • Simple Input : You can use this mode to insert the new row without writing a DML statement. Here a form based row fields entry is loaded, where you can enter the value of every field in the row.
    • For binary data type, the string typed in should be a valid Base64 encoding of a binary value or select the file to upload in the desired column.
    • For fixed binary data type, the string typed in should be a valid Base64 encoding of a binary value or upload the file according to the size specified for the column.

    Note:

    The file format you upload for binary data type should have the .bin extension.
  • Advanced JSON Input : You can use this mode to insert a new row into the table by supplying a JSON Object containing the column name and its corresponding value as key-value pairs. The input can also be a complex data type i.e. array, map, record.
• Click Insert Row.

Modify Data - UPDATE ROW/DELETE ROW:

• Locate the Table Explorer, and click the Refresh Schema to reload the schema.
• Click on the table where data needs to be modified.
• In the textbox on the right under SQL>, enter the SQL statement to fetch data from your table. Click > to run the query.
• To view individual cell data separately, click the table cell.
• To perform DML operations like Update and Delete Row, right-click on the particular row. Pick your option from the context-menu that appears.
  • Delete Row : A confirmation window appears, click Ok to delete the row.
  • Update Row : A separate HTML panel opens below the listed rows, containing the column names and its corresponding value in a form-based entry or provide the input as ON key-pair object. You can choose either of the two methods and supply new values.

    Note:

    In any row, PRIMARY KEY and GENERATED ALWAYS AS IDENTITY columns cannot be updated.

Executing SQL Queries for a Table

• Locate the Table Explorer, and click the Refresh Schema to reload the schema.
• Right click on the table and choose Browse Table.
• In the textbox on the right under SQL>, enter the SELECT statement to fetch data from your table.
• Click > to run the query. The corresponding data is retrieved from the table.
• Right click on any row and click Download JSON. The single row gets downloaded into a JSON file. In the dialog box, navigate to the location where you want to save the file and click Save.
• Click Download Query Result to save the complete result of the SELECT statement as a JSON file. In the dialog box, navigate to the location where you want to save the file and click Save.
• Click Fetch All Records to retrieve all data from the table.
• Click Show Query Plan to view the execution plan of the query.
• Click the Previous Commands dropdown, to view the recently executed SQL statements that had provided an output.

  Note:

  The dropdown will only show SQL statements related to the table you are dealing with.

Table Explorer

• In the Table Explorer window, you can verify the data type of a particular column. Locate the particular column and the data type is followed by the column name.