Altova DatabaseSpy 2024 Professional Edition

Adding and Copying Rows

Home Prev Top Next

New rows can be added in the result grid of the SQL Editor, provided that data was retrieved in Editing mode, see Retrieving and Editing Data. The toolbar of the Result window provides the Append a new row command for this purpose. This command inserts an empty row where you can enter data and save the record using the Commit button.

dbs_append_row_example

You can define in the SQL Editor options that the respective default values of the individual columns be inserted into new rows. In the example above, only the columns created_at and updated_at have a default value defined; DatabaseSpy hence populates them automatically. If the default property has not been set accordingly, a blank row will be inserted into the result grid.

 

In addition to editing rows manually, you can also paste into the result grid entire cell ranges (rows, columns, or both) from external files such as Microsoft Excel spreadsheets. The external cell ranges can be pasted over existing cells of the Result grid.

 

To paste clipboard data from external files into new rows, first click Append a new row ( dbs_ic_append ) to create the required number of rows, and then paste the clipboard cells at the desired location. The Paste command is available both as a keyboard shortcut (Ctrl+V) or from the Edit | Paste menu.

 

If you are pasting data from a different result grid of DatabaseSpy, it is possible to create the required number of new rows automatically as you paste data. To do this, right-click the Result window and select the Paste as new rows (Ctrl+Shift+V) command from the context menu.

 

Adding new records to incomplete selections of data

When not the entire table but a collection of individual columns is selected for data editing, the following restrictions apply when a new row is to be inserted into the result grid:

 

The column(s) containing the table's primary key must be included to make data editing possible.

All columns with the "Nullable" property not set must be included unless they have a default value defined.

 

Using the table below as example, let's assume that both the NAME and the STATUS columns are not nullable, but only the STATUS column has a default value ("Standard") defined.

tab_result-new-line

We could further assume that each new customer is automatically assigned status "Standard" and thus omit the STATUS column when creating new customers. Adding new rows is possible in this case since the default value is automatically assigned when the record is committed to the database.

tab_result-add-line

When checking the results of the entire table, you will see that the default value "Standard" has been automatically inserted into the Status column of the new record.

tab_result-add-line-full

However, if you omit, for instance, the Name column, a warning message ("Editing of data is limited for this statement") is displayed when you select the Edit Data command. If you do not want to have this dialog box displayed, select the Don't show this dialog again! check box or disable this feature in the SQL Editor options. After clicking OK, you can still edit data in the Result window but the Append a new row ic_add_line button and context menu option are disabled.

tab_result-add-line-disabled

 

Copying rows

If you need to duplicate some of the data stored in your database tables, DatabaseSpy provides a feature allowing you to copy data from the result grid and append that content to the grid in a new row. You can either copy an entire row or individual cells. If you copy data from more than one row and append it as new rows, DatabaseSpy will create as many new rows as there were to host the original data.

To copy a row and append it to the result grid:

1.Make sure that the Result window is in the Editing mode.

2.In the Result window, right-click into the row you want to copy and choose Selection | Row from the context menu. Alternatively, click several individual data cells using Ctrl+Click.

3.Right-click anywhere into the Result window and choose Copy selected cells from the context menu or click Ctrl+C.

4.Right-click anywhere into the Result window and choose Paste as new rows from the context menu or click Ctrl+Shift+V.

 

Copying records and using data outside DatabaseSpy

You can use the clipboard to export selected records to any other application that supports the clipboard. It might be helpful to include also the headers of the selected data, even if only a fraction of a record set is copied. The context menu in the Result tab provides the Copy selected cells with header command for this purpose. The corresponding header will be copied to the clipboard together with each selected cell.

© 2017-2023 Altova GmbH