View and edit data in the Grid View mode

Data Viewer lets you view and edit data directly in the grid, just like in an Excel spreadsheet.

Retrieve data for viewing

  • Execute a query that returns data.
  • Alternatively, right-click the required table to open the shortcut menu and select Retrieve Data.

Viewing data

Results output patterns

There are two patterns for the results output: paginal mode and infinite scrolling.

Paginal mode

The paginal mode splits data into pages and is used to display data with a large number of records.

By default, the paginal mode displays 1,000 records per page. If your query returns more than 1,000 records and you wish to view them all on a single page, you can set a custom number of records either in the Data Editor or the Options window.

From the Data Editor

On the Data Editor toolbar, enter a number of records larger than the expected one.

Set a custom number of records in the Data Editor

From the Options window

1. Go to the Tools menu and select Options.

2. In the window that opens, navigate to Data Editor > General and specify the value in the Initial page size field.

3. To save the changes, click OK.

Set a custom number of records in the Options window

Infinite scrolling

The infinite scrolling pattern is activated when the paginal mode is off. The pattern loads data continuously as the user scrolls down the page, allowing all data to be retrieved at once.

If a table contains more than 1000 records and the paginal mode is off, Query Builder can return 302+ records after the SELECT statement execution. It means that the first 302 records are read in the finishing mode, and the + sign means that there are more to load. To see the rest of the data, scroll the data in the grid or press Ctrl + End.

Note

If you want to retrieve all data from a table with a large number of records, you will get a message warning you about the possibility of the Out of memory exception. You can choose to continue retrieving data, cancel retrieving data, or turn on the Paginal mode.

Manipulate the results output

You can use a rich set of data management options from the shortcut menu.

Viewing data - shortcut menu

To change the column order and width, as well as group them by order, drag column headers to the required place. Additionally, you can fit the column width to the column content by right-clicking the required header and selecting Best Fit on the shortcut menu. To adjust the widths of all columns at once, select Best Fit (all columns). Note that this operation can take some time if there are many records in the grid.

You can set the background and foreground colors for the grid rows. This can be done separately for odd and even rows, focused rows, selected rows, focused cells, or group rows.

Data rows can be viewed, edited and printed as cards. To learn more, refer to Viewing and editing data in the Card View mode.

To simplify the subsequent data analysis, you can use the following features:

Refresh data

To refresh data, select Refresh on the Data menu (for the data view), on the shortcut menu, or on the toolbar. To abort an overly long refresh operation, select Stop Refresh on the Data menu (for the data view) or on the toolbar.

In case the query text has been edited, and changes have been applied, run the Execute command anew instead of refreshing the gird to get updated data.

View and edit data in Data Viewer

You can view and edit data in the built-in Data Viewer. To do that, right-click the required cell and select Data Viewer on the shortcut menu. Alternatively, use the CTRL+W, R key combination.

Data Viewer

The Data Viewer toolbar has the following options:

Data Viewer toolbar

Save Data Save Data allows saving data to a file.

Copy, Paste, Cut These options let you Copy, Paste, and Cut data, respectively.

The following options allow viewing data in multiple formats:

Hexadecimal View Hexadecimal View

Text View Text View

XML View XML View

HTML View HTML View

Web View Web View

Rich Text View Rich Text View

PDF View PDF View

JSON View JSON View

Image View Image View

View White Spaces If the View White Spaces option is on, it makes white spaces visible.

Word Wrap This option lets Word Wrap in the Data Viewer window.

Edit data in Data Editor

When Query Builder executes a statement returning a result set, the retrieved data is displayed in the Data Editor of the same SQL document.

Note

To prevent data loss due to accidental editing, the query result grid by default is set to the read-only mode. To turn on editing, select the desired table from the Table dropdown instead of the read-only option. Enable editing mode

The Data Editor toolbar has the following options:

Data Editor toolbar

Refresh data Refresh Data and Stop Refresh Data let you refresh and stop refreshing data in the grid, respectively.

Cached Updates Mode Cached Updates Mode allows you to apply data updates in a single batch, thus emulating a separate transaction. If this mode is turned on, your data changes will be stored locally on the client side until you click Apply Changes. If there is no need for the changes to be applied, click Cancel Changes. If you close a document with Cached Updates Mode activated, all the changes made since the latest Apply Changes command will be canceled.

Paginal Mode Paginal Mode allows switching to the Paginal mode.

Switching between pages Go to First Page / Go to Previous Page /Go to Next Page/ Go to Last Page allow switching between pages and specify the number of records per page.

Grid and Card View modes Switch to Grid View and Switch to Card View allow switching between Grid View and Card View.

Show Group by Box Show Group by Box allows grouping data by column.

Auto-search mode Auto-search mode activates the auto-search mode.

Data export Data Export turns on data export.

Switching from read-only This dropdown menu allows switching from the read-only mode to editing.

Note

Computed columns always remain in the read-only mode and cannot be edited.

The bottom of the grid has the following options:

Data Editor toolbar

Records let you navigate down the grid one record at a time, or instantly get to the first or the last record in the grid.

Append and delete let you append or delete records from the grid.

Apply or cancel changes let you apply or cancel changes made during editing.

Note

If you are editing data in a table with a foreign key, you can view the data of the related table using the foreign key lookup feature from the Data Editor.

The data of the related table is shown after you click the button next to the field you want to edit. The corresponding record in the related table will be highlighted, as shown in the screenshot below.

Foreign key lookup

Select data

To copy or edit data, you need to select it.

Data can be selected just like cells in a spreadsheet. To select data, do one of the following actions:

  • Move the mouse pointer across the grid holding the left mouse button.

  • Click the first cell of the required data range, press and hold SHIFT, and then select the last cell. A rectangular range of cells will be selected.

  • Use CTRL+A to select all cells in the grid.

Using the CTRL key, you can include/exclude cells from selection. Any number of rows may be copied and pasted to another application (for example, a spreadsheet). However, when you paste data in the data grid, only the first row will be pasted.

Copy and paste data

You can copy selected data by selecting Copy or Copy With Headers on the shortcut menu.

Alternatively, you can copy selected data using shortcuts:

  • Use CTRL+C to copy data.

  • Use CTRL+SHIFT+C to copy data with headers.

Additionally, you can select Copy Data As on the shortcut menu to Copy Column Names Only or Copy Column Names and Types.

Similarly, you can paste data by selecting Paste or Paste As New Records on the shortcut menu or using the CTRL+V shortcut.

Insert and delete data

For these operations, you can use the shortcut menu or the corresponding options under the grid. For example, you can do the following:

  • To insert NULL (instead of an empty string), select Set Value To > Null on the shortcut menu. You can also use the CTRL+0 key combination.

Note

Use the Set Value To menu to quickly set an empty string, zero, or the current date.

  • To add a new record, select the Append option on the shortcut menu or click the + button under the grid.

  • To delete a record from the grid, select the Delete option on the shortcut menu or click the - button under the grid. You can also use the CTRL+DEL key combination.

Note

Some queries return read-only data; for example, queries with aggregate functions (e.g. COUNT, AVG) or data, retrieved from non-updatable views. Such data cannot be edited. It is also impossible to edit the results of multiple SELECT statements.

Row and cell indicators

When working with the grid, you can see particular indicators next to the focused cell. These indicators reflect the current editing state.

Focused row The row is focused.

Editing The row is being edited.

Row edited The row has been edited.

Invalid value An invalid value has been entered into a cell. You must enter a valid value or press the ESCAPE key to cancel changes.

Copy and paste in spreadsheet-like data editor

When you open the Data Editor in dbForge Query Builder, it provides a spreadsheet-like interface where data is organized in rows and columns. The Copy-Paste feature allows you to copy data from other sources, such as text files, Excel tables, or other tables within the app, as well as vice versa: copy data from Query Builder and paste it into other applications.

Copy and paste in Data Editor

Copying and pasting in dbForge Query Builder is similar to the standard process in other spreadsheet applications. You can select cells, rows, or columns containing the necessary data and then copy them to the clipboard by pressing Ctrl+C.

Next, you navigate to the location where you wish to paste the data in Query Builder and perform the paste operation by pressing Ctrl+V.

Note

It is important to note that for this approach to function correctly, the cached update mode must be on.

Want to Find out More?

Overview

Overview

Take a quick tour to learn all about the key benefits delivered by dbForge Query Builder for SQL Server.
Request a demo

Request a demo

If you consider employing the Query Builder for your business, request a demo to see it in action.
Ready to start using dbForge Query Builder for SQL Server?