View and edit data in the Grid View mode

The Data Viewer of Data Compare for SQL Server lets you view and edit data directly in the grid, just like in an Excel spreadsheet.

Retrieve data for viewing
Manipulate the results output
Refresh data
Edit data in Data Editor
Select data
Copy and paste data
Insert and delete data
Row and cell indicators

Retrieve data for viewing

To retrieve data, do any of the following steps:

On the SQL toolbar, click Results as Grid before executing a query that returns data.
Execute the query to return the data.

Viewing data

Note

If you want to retrieve all the data from a table with a large number of records, a message warning you about the possibility of the Out of memory exception is shown, and you can choose either of the following actions - continue retrieving data, cancel retrieving data, or enable the paginal mode. By default, the paginal mode is enabled in data view with 1000 records per page so that only 1000 records are displayed at once. For more information, see Paginal mode.

Manipulate the results output

To change the column order and width, as well as group them by order, drag column headers to the required place. In addition, you can use a rich set of data management options from the shortcut menu.

Right-click the column header and select the required option:

Sort Ascending or Sort Descending to sort the column in an ascending or descending order, respectively.
Group By This Column to group data by the current column and automatically display it as collapsable groups.
Show/Hide Group By Box to display or hide the group data by the current column.
Hide this Column to hide the column from the Server Profile document.
Visible Columns to hide or show column data in the SQL document.
Best Fit to adjust the column width to the column content.
Best Fit (all columns) to adjust widths of all columns simultaneously. Note that this operation can take some time if there are many records in the grid.
Filter Editor to open the Filter Editor window to set filter criteria.

Manage columns

Data rows can be viewed, edited and printed as cards. For more information, see View and edit data in the Card View mode.

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, to get updated data, run the Execute command anew instead of refreshing the grid.

View and edit data in Data Viewer

You can view and edit data in the built-in Data Viewer. To open it, right-click the required cell in the grid and select Data Viewer. Alternatively, use the CTRL+W, R key combination. In the Data Viewer, you can save data to a file, copy/paste or cut data, view data in multiple formats, as well as make white spaces visible.

Data Viewer

Edit data in Data Editor

When dbForge Event Profiler executes a statement, the retrieved data is displayed in the same SQL document in Data Editor.

The Data Editor toolbar has the following options:

Data Editor toolbar

Refresh Data and Stop Refresh Data allow you to refresh data and stop refreshing it in the grid, respectively.

Cached Updates Mode, Apply Changes, and Cancel Changes

Cached Updates Mode lets you control data editing within a single database object. If you turn on this mode for an object, your changes will be stored locally on the client side until you click Apply Changes. If you want to discard the changes, click Cancel Changes. If you close a document in the Cached Updates Mode, you will cancel all the changes made since the latest Apply Changes command.

Paginal Mode allows switching to the paginal mode.

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 the result grid views.

Show Group by Box displays the group data by the current column.

Auto-search mode turns on the auto-search mode.

The bottom panel of the grid has the following options:

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 allow you to append or delete records from the grid.

let you apply or cancel changes made during editing.

Select data

In the grid, you can copy or edit the selected data. 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 can help you 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.

Note

Some queries return read-only data, for example, queries with aggregate functions (count, avg etc.), or data, retrieved from non-updatable views. Such data cannot be edited. It is also impossible to edit the result of executing of the script with several select statements.

Copy and paste data

To copy the selected data, right-click it and select Copy or Copy With Headers. Alternatively, you can use CTRL+C to copy the selected data or use CTRL+SHIFT+C to copy data with headers.

In addition, select Copy Data As on the shortcut menu to Copy Column Names Only or Copy Column Names and Types. Similarly, select Paste or Paste As New Records on the shortcut menu or use the CTRL+V shortcut to paste data.

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. Alternatively, 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 Append on the shortcut menu or click + under the grid.
To delete a record from the grid, select Delete on the shortcut menu or click - under the grid. You can also use the CTRL+DEL key combination.