Perform DML operations using Visual Studio Code

You can add data, modify existing data and query data from tables using Visual 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.

Note: You can add a row in JSON collection table only through Advanced JSON Input mode. For more information on how to create JSON collection table, see Using JSON Collection Tables

• 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 : You can update in the separate HTML panel that opens below the listed rows, displaying the column names along with their current value in a form-based entry. The other option to edit is to provide the input as a key-value. You can choose either of the two methods and supply new values.

    Note:

    • For updating the row data in a JSON collection table, you can modify the data only using Advanced JSON Input mode.
    • 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.

• While writing a query, the system highlights the syntax and displays a list of possible completions at the insertion point. You can select the desired option to autocomplete the code based on the context.

• After writing your query, right click anywhere in the textbox and select Prettify SQLto format the code, improving its spacing, line wrapping, and increasing its overall readability.

• Click > to run the query. The corresponding data is retrieved from the table.

• Click on any row entry with a JSON data type column to open a dialog box displaying the JSON fields in an interactive tree structure. Click ‘+’ to expand or ‘-’ to collapse the structure.

  • In addition to the dialog box, a search field appears at the top-right corner. This allows you to search for any value in the JSON tree in real time, even within collapsed tree structures. Use the up or down arrow to navigate to the previous or next search result, respectively.

• 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.