Developers can create a new row in the current table using an Insert Values query. When they create an Insert Values query, they specify:
– The database table to add the row to.
– The columns whose contents they want to add.
– The value or expression to insert into the individual columns.
For example, the following query adds a row to the pc_monitors table, specifying values for the title, type, publisher, and price:
INSERT INTO pc_monitors
( monitor_id, vendor, type, price )
VALUES ( ‘MON8899’, ‘HP’, ‘LCD’, 234.56 )
When developers create an Insert Value query, the Criteria pane changes to reflect the only options available for inserting a new row: the column name and the value to insert. Developers cannot undo the action of executing an Insert Values query. As a precaution, they should back up the data before executing the query. When developers working with SQL Server “Denali”, MS SQL Server 2008 R2 and MS SQL Server 2005 want to create an Insert values queries, they should follow the next steps:
1. Add the table they want to update to the Diagram pane.
2. From the Query Designer menu point to Change Type, and then click Insert Values. If more than one table is displayed in the Diagram pane when they start the Insert Values query, the Query and View Designer displays the Choose Target Table for Insert Values Dialog Box to prompt them for the name of the table to update.
3. In the Diagram pane, click the check box for each column for which they want to supply new values. Those columns will show in the Criteria pane. Columns will be updated only if they add them to the query.
4. In the New Value column of the Criteria pane, enter the new value for the column. They can enter literal values, column names, or expressions. The value must match (or be compatible with) the data type of the column they are updating. The Query and View Designer cannot check that a value fits within the length of the column they are inserting. If they provide a value that is too long, it might be truncated without warning
When developers execute an Insert Values query, no results are reported in the Results Pane. Instead, a message appears indicating how many rows were changed.