Managing Data

After you create a database, tables, and indexes, you can put data into the tables and work with it—adding, changing, transferring, and deleting as necessary.

See the Reference Manual: Commands.

You can also add data to a table by transferring it from a file using the bulk-copy utility program, bcp. See the Utility Guide.

You can use insert, update, or delete to modify data in one table per statement. Transact-SQL enhancements to these commands let you base modifications on data in other tables, and even other databases.

The data modification commands also work on views, with some restrictions.

Database owners and the owners of database objects can use the grant and revoke commands to specify the users who are allows to execute data modification commands.Permissions or privileges can be granted to individual users, groups, or the public for any combination of the data modification commands. Permissions are discussed in, Managing User Permissions, in the System Administration Guide: Volume 1.

• Referential Integrity
  Keeping data modifications consistent throughout all tables in a database is called referential integrity.
• Transactions
  A transaction is data modification that is performed on a database. A copy of the old and new state of each row affected by each data modification statement is written to the transaction log.
• Sample Databases
  SAP ASE includes the pubs2 and pubs3 sample databases, which are used for most examples in the documentation.
• Add New Data
  Use the insert command to add rows to a database.
• Create Nonmaterialized, Non-Null Columns
  Nonmaterialized columns exist virtually, but are not physically stored in the row. Use nonmaterialized columns the same as any other column, selecting, updating, and referring to them in SQL queries, or using them as index keys.
• Change Existing Data
  Use the update command to change single rows, groups of rows, or all rows in a table. As in all data modification statements, you can change the data in only one table at a time.
• Change text, unitext, and image data
  Use writetext to change text, unitext, or image values when you do not want to store long text values in the database transaction log.
• Truncate Trailing Zeros
  The disable varbinary truncation configuration parameter controls whether trailing zeros are included at the end of varbinary and binary null data.
• Transfer Data Incrementally
  The transfer command allows you to transfer data incrementally, and, if required, to a different product.
• Delete Data
  delete works for both single-row and multiple-row operations.
• Delete All Rows from a Table
  Use truncate table to delete all rows in a table. truncate table is almost always faster than a delete statement with no conditions, because the delete logs each change, while truncate table logs only the deallocation of entire data pages.