Skill 1.4: Modify Data
What statement is used to *delete* rows from a table?
*DELETE* statement. You can delete rows from a table. You can optionally specify a predicate to restrict the rows to be deleted. The general form of a *DELETE* statement looks like the following: *DELETE FROM* <table> *WHERE* <predicate>; If you don't specify a predicate, all rows from the target table are deleted. As with unqualified updates, you need to be especially careful about accidentally deleting all rows by highlighting only the *DELETE* part of the statement, missing the *WHERE* part.
What statement inserts the result set (or sets) returned by a dynamic batch or a stored procedure into the specified target table?
*INSERT EXEC* statement.
What statement inserts *the result set returned by a query* into the specified target table?
*INSERT SELECT* statement.
What statement can we use to insert one or more rows into a target table based on *value expressions*?
*INSERT VALUES* statement.
Which clause *defines the target table* for the operation in the *MERGE* statement?
*MERGE INTO <target table>* This clause defines the target table for the operation. You can alias the table in this clause if you want.
What statement can you use to *merge data* from a source table or table expression into a target table?
*MERGE* statement. You can merge data from a source table or table expression into a target table. The general form of the MERGE statement is as follows:
Which clause, in the *MERGE* statement, *specifies a predicate* that matches rows between the source and the target and defines whether a source row is or isn't matched by a target row?
*ON <merge predicate>* In this clause, you specify a predicate that matches rows between the source and the target and defines whether a source row is or isn't matched by a target row. Note that this clause isn't a filter like the ON clause in a join.
What statement creates the target table based on the definition of the source and inserts the result rows from the query into that table?
*SELECT INTO* statement.
What optimized (faster) statement can you use to delete all rows from the target table or partition.
*TRUNCATE TABLE* statement. TRUNCATE TABLE is an optimized statement that deletes all rows from the target table or partition. Unlike the DELETE statement, the TRUNCATE TABLE statement doesn't support a filter. Also, whereas the DELETE statement is fully logged and therefore tends to be quite slow, the TRUNCATE table statement uses an optimized logging mode and therefore is significantly faster.
What statement enables you to update existing rows in a table?
*UPDATE* statement. The standard UPDATE statement has the following form: UPDATE <target table> SET <col 1> = <expression 1>, ..., <col n> = <expression n> WHERE <predicate>; You specify the target table name in the UPDATE clause. If you want to filter a subset of rows, you indicate a WHERE clause with a predicate. Only rows for which the predicate evaluates to true are updated. Rows for which the predicate evaluates to false or unknown are not affected. An UPDATE statement without a WHERE clause affects all rows. You assign values to target columns in the SET clause. The source expressions can involve columns from the table, in which case their values before the update are used.
Which clause *defines the source table* for the operation in the *MERGE* statement?
*USING <source table>* This clause defines the source table for the operation. You can alias the table in this clause if you want. Note that the USING clause is designed similar to a FROM clause in a SELECT query, meaning that in this clause you can define table operators like joins, refer to a table expression like a derived table or a common table expression (CTE), or even refer to a table function like OPENROWSET. The outcome of the USING clause is eventually a table result, and that table is considered the source of the merge operation.
Which clause, in the *MERGE* statement, defines an action to take when a *source row is matched by a target row*?
*WHEN MATCHED [AND <predicate>] THEN <action>* This clause defines an action to take when a source row is matched by a target row. Because a target row exists, an INSERT action isn't allowed in this clause. The two actions that are enabled are UPDATE and DELETE. If you want to apply different actions in different conditions, you can specify two WHEN MATCHED clauses, each with a different additional predicate to determine when to apply an UPDATE and when to apply a DELETE.
Which clause, in the *MERGE* statement, defines an action to take when a *target row exists, but it is not matched by a source row*.
*WHEN NOT MATCHED BY SOURCE [AND <predicate>] THEN <action>* This clause is a *proprietary extension by Microsoft* to the standard MERGE statement syntax. It defines an action to take when a target row exists, but it is not matched by a source row. Because a target row exists, you can apply either an UPDATE or a DELETE, but not an INSERT. If you want, you can have two such clauses with different additional predicates that define when to use an UPDATE and when to use a DELETE.
Which clause, in the *MERGE* statement, defines what action to take when a *source row is not matched by a target row*.
*WHEN NOT MATCHED [BY TARGET] [AND <predicate>] THEN <action>* This clause defines what action to take when a source row is not matched by a target row. Because a target row does not exist, the only action allowed in this clause (if you choose to include this clause in the statement) is INSERT. Using UPDATE or DELETE holds no meaning when a target row doesn't exist. You can still add an additional predicate that must be true in order to perform the action.
What is a major drawback of the *DELETE* statement?
A *DELETE* statement is *fully logged* and as a result, large deletes can take a *long time to complete*, and much longer to roll back if you need to terminate them.
If you're using a *cursor* to iterate through rows of a table, what syntax can you use to delete the table row that the *cursor* is currently positioned on? Table to delete from: *dbo.MyTable*, Cursor to be used: *MyCursor*.
Suppose that you iterate through rows of a table called MyTable using a cursor called MyCursor. Based on some condition that is met, you want to delete the current row. You achieve this using the statement:
Suppose that you had a partitioned table called *MyTable* and you wanted to truncate partitions *1, 2* and *11 to 20*. How would you achieve this?
Suppose that you had a partitioned table called MyTable and you wanted to truncate partitions 1, 2 and 11 to 20. You would achieve this with the following code:
Write a query that will modify the row (*orderid = 10250 & productid = 51*), increasing the discount by *five percent*, and collect the new discount into a variable called *@newdiscount*. Table to be updated: *Sales.MyOrderDetails*, Column to be updated: *discount*.
Suppose that you need to modify the row, increasing the discount by five percent, and collect the new discount into a variable called @newdiscount. You can achieve this using a single UPDATE statement, as follows. As you can see, the UPDATE and WHERE clauses are similar to those you use in normal UPDATE statements. But the SET clause uses the assignment @newdiscount = discount += 0.05, which is equivalent to using @newdiscount = discount = discount + 0.05. The statement assigns the result of discount + 0.05 to discount, and then assigns the result to the variable @newdiscount. The last SELECT statement in the code returns the new discount 0.200.
You need to perform a multi-row insert into a target table that has a column with an identity property. You need to capture the newly generated identity values for further processing. How can you achieve this?
Use the OUTPUT clause and write the newly generated identity values along with any other data that you need from the inserted rows aside, for example into a table variable. You can then use the data from the table variable in the next step where you apply further processing.
When should you use the *WITH VALUES* clause explicitly as part of adding a column to a table?
When the column is defined as a nullable one, and you want to apply the default expression that is associated with the column in the new rows, you need to specify the WITH VALUES clause explicitly. If the column is defined as NOT NULL, and you associate a default expression with it, the default expression is applied even when not specifying the WITH VALUES clause explicitly.
When would you use *UPDATE* based on *JOIN*?
When you might want to update rows in a table, and refer to related rows in other tables for filtering and assignment purposes. *Note:* This is strictly the *T-SQL* feature.
What are the differences between DELETE and TRUNCATE TABLE?
DELETE supports a filter, is fully logged, and does not reset the current identity value. TRUNCATE TABLE has no filter, is minimally logged and therefore much faster than DELETE, and does reset the current identity value. Unlike DELETE, TRUNCATE TABLE is disallowed if there's an indexed view based on the table, or a foreign key pointing to the table, even if there are no related rows in the referencing table.
Write a query that will *add a day* to the order date of all orders that were handled by *employee 7*. Table to update: *Sales.MyOrders*, Column to update: *orderdate*, Columns to output: *orderid, orderdate AS old_orderdate, orderdate AS new_orderdate*, Column to filter on: *empid*.
You can use the OUTPUT clause to return information from modified rows in an UPDATE statement. With updated rows, you have access to both the old and the new images of the modified rows. To refer to columns from the original state of the row before the update, prefix the column names with the keyword deleted. To refer to columns from the new state of the row after the update, prefix the column names with the keyword inserted. As an example, the following UPDATE statement adds a day to the order date of all orders that were handled by employee 7: The code uses the OUTPUT clause to return the order IDs of the modified rows, in addition to the order dates—both before and after the update.
What is a possible drawback of the proprietary *T-SQL UPDATE* syntax based on *JOINs*?
You should be aware that the proprietary T-SQL UPDATE syntax based on joins could be *nondeterministic.* The statement is *nondeterministic* when multiple source rows match one target row. Unfortunately, in such a case, SQL Server doesn't generate an error or even a warning. Instead, SQL Server silently performs a *nondeterministic UPDATE* where it arbitrarily chooses one of the source rows.
How are *SET* expressions treated in the same *UPDATE* statement?
Expressions that appear in the *same logical phase* are *treated as a set, in an all-at-once manner*.
Write a query that will add a new column *requireddate* to the *Sales.MyOrders* table. The column has the following attributes: *DATE* data type, *NOT NULL*, CONSTRAINT with a default value *'19000101'*.
If the table is empty, you can add a column that doesn't allow NULLs and also doesn't get its values somehow automatically. If the table isn't empty, such an attempt fails. To demonstrate this, run the following code: Observe that in order to add a column to a nonempty table, the column either needs to allow NULLs, or somehow get its values automatically. For instance, you can associate a default constraint with the column when you add it. You can also indicate that you want the default expression to be applied to the existing rows by adding the WITH VALUES clause as follows: Note that if the column is defined as NOT NULL as in our case, the default expression is applied with or without this clause. If the column allows NULLs, without the clause a NULL is used and with the clause the default expression is used.
If you need to delete all rows from a table or a partition but leave the table definition in place, the recommended tool to use is the *----------* statement.
If you need to delete all rows from a table or a partition but leave the table definition in place, the recommended tool to use is the TRUNCATE TABLE statement.
Write a query that will add a five percent discount to the current row's discount in the *Sales.MyOrderDetails* table. Table to be updated: *Sales.MyOrderDetails*. Columns to be used: *discount*.
If you're using a cursor to iterate through rows of a table, you can modify the table row that the cursor is currently positioned on by using the filter WHERE CURRENT OF <cursor_ name>. For example, suppose that you iterate through rows of a table called MyTable using a cursor called MyCursor. Based on some condition that is met, you want to increase the current row's discount by five percent. You achieve this using the statement:
What is the syntax for *adding a column* to a table?
In order to add a column to a table, you use the following syntax:
What is the syntax for *altering a column* in a table?
In order to alter a column, you use the following syntax:
What is the syntax for *dropping a column* from a table?
In order to drop a column from a table, you use the following syntax:
Write a query that will delete orders placed by customers from the *USA*. Table to delete from: *Sales.MyOrders*, Tables to be joined: *Sales.MyCustomers*, on *custid* key Column to be filtered on: *country* from table *Sales.MyCustomers*
Much like the proprietary syntax that T-SQL supports for an UPDATE statement based on a join, T-SQL supports similar syntax for a DELETE statement based on a join. The idea is to allow you to delete rows from one table based on the presence of related rows in other tables, with the ability to apply a filter predicate that is based on attributes in the related tables. The following statement deletes orders placed by customers from the US. Notice that there are two FROM clauses. The second is mandatory and is similar to the FROM clause in a SELECT statement. That's where you apply table operators like joins. The first FROM clause appears right after the DELETE clause and is optional. That's where you specify the target for the delete. In our case it's the alias O representing the Sales.MyOrders table.
What are the differences between the *TRUNCATE TABLE* and *DELETE* statemens?
■■ Unlike the DELETE statement, the TRUNCATE TABLE statement doesn't support a filter. ■■ You cannot assign direct TRUNCATE TABLE permissions, rather at minimum you need ALTER permission on the target table. A common workaround is to place the TRUNCATE TABLE statement in a module, like a stored procedure, and assign the required permission to the module using the EXECUTE AS clause. ■■ If there's a column with an identity property in the target table, DELETE doesn't reset the property whereas TRUNCATE TABLE does. ■■ If there are any foreign keys pointing to the target table, a DELETE statement is supported as long as there are no related rows in the referencing table, but a TRUNCATE TABLE statement isn't. You need to first drop the foreign keys, truncate the table, and then recreate the foreign keys. ■■ If there are any indexed views based on the table, a DELETE statement is supported whereas a TRUNCATE TABLE statement isn't.
Create a new table in the *TSQLV4* database and in the *Sales* schema. Name your new table *MyOrders*. Include the following columns: *orderid* - integer data type, not nullable, identity column meaning it is self-increasing by 1 (1, 2, 3, .....), with a constraint PK_MyOrders_orderid, this column is a primary key; *custid* - integer data type, not nullable; *empid* - integer data type, not nullable; *orderdate* - date data type, not nullable with a default constraint DFT_MyOrders_orderdate that returns the current system's date.; *shipcountry* - variable UTC string of length 15 data type, not nullable; *freight* - money data type, not nullable
Observe that the orderid column has an identity property defined with a seed 1 and an increment 1. This property generates the values in this column automatically when rows are inserted. As an alternative to the identity property you can use a sequence object to generate surrogate keys. Also observe that the orderdate column has a default constraint with an expression that returns the current system's date.
Write a query that will add a five percent discount to the order lines in the *Sales.MyOrderDetails* table associated with order *10251*. Table to be updated: *Sales.MyOrderDetails*. Columns to be used: *discount & orderid*.
The following code demonstrates an UPDATE statement that adds a five percent discount to these order lines: Notice the use of the compound assignment operator discount += 0.05. This assignment is equivalent to discount = discount + 0.05. T-SQL supports such enhanced operators for all binary assignment operators: += (add), -= (subtract), *= (multiply), /= (divide), %= (modulo), &= (bitwise and), |= (bitwise or), ^= (bitwise xor), += (concatenate).
Write a query that will delete all order lines containing *product ID 11* from the *Sales.MyOrderDetails* table? Table to be deleted from: *Sales.MyOrderDetails*, Column to be filtered on: *productid*.
The following example deletes all order lines containing product ID 11 from the Sales. MyOrderDetails table. You get a message indicating that 38 rows were affected.
When does the attempt to *alter* the column fail?
There are a number of cases where the attempt to alter the column fails (partial list): ■■ When used in a primary key or foreign key constraint. ■■ When used in a check or unique constraint, unless you're just keeping or increasing the length of a variable-length column. ■■ When used in a default constraint, unless you're changing the length, precision, or scale of a column as long as the data type is not changed.
Write a query that will add a five percent discount to the order lines associated with orders placed by customers from *Norway*. The rows you need to modify are in the *Sales.MyOrderDetails* table. But the information you need to examine for filtering purposes is in rows in the *Sales.MyCustomers* table. In order to match a customer with its related order lines, you need to join *Sales.MyCustomers* with *Sales.MyOrders*, and then join the result with *Sales.MyOrderDetails*. Note that it's not sufficient to examine the *shipcountry* column in *Sales.MyOrders*; instead, you must check the country column in *Sales.MyCustomers* table. Table to be updated: *Sales.MyOrderDetails*. Tables to be joined: *Sales.MyCustomers, Sales.MyOrders*. Columns to be used: *custid, orderid, country*.
As an example, suppose that you want to add a five percent discount to order lines associated with orders placed by customers from Norway. The rows you need to modify are in the Sales.MyOrderDetails table. But the information you need to examine for filtering purposes is in rows in the Sales.MyCustomers table. In order to match a customer with its related order lines, you need to join Sales.MyCustomers with Sales.MyOrders, and then join the result with Sales.MyOrderDetails. Note that it's not sufficient to examine the shipcountry column in Sales. MyOrders; instead, you must check the country column in Sales.MyCustomers. In order to perform the desired update, simply replace the SELECT clause from the last query with an UPDATE clause, indicating the alias of the table that is the target for the UPDATE (OD in this case), and the assignment in the SET clause, as follows: Note that you can refer to elements from all tables involved in the statement in the source expressions, but you're allowed to modify only one target table at a time.
Write a query that will merge the contents of the *CustomersStage* table (the source) into the *Customers* table (the target). More specifically, you need to add customers that do not exist and update the customers that do exist. Update only if at least one column value is different, there is a way to achieve this. Table to merged into: *dbo.Customers*, Table to merge: *dbo.CustomersStage* Columns in both tables: *custid, companyname, phone, address*
Suppose you need to merge the contents of the CustomersStage table (the source) into the Customers table (the target). More specifically, you need to add customers that do not exist and update the customers that do exist. Going back to the first MERGE example, which updates existing customers and adds nonexistent ones, you can see that it doesn't check whether column values are actually different before applying an update. This means that a customer row is modified even when the source and target rows are identical. If you want to apply the update only if at least one column value is different, there is a way to achieve this. The MERGE statement supports adding a predicate to the different action clauses by using the AND option; the action will take place only if the additional predicate evaluates to TRUE. In this case, you need to add a predicate under the WHEN MATCHED AND clause that checks that at least one of the column values is different to justify the UPDATE action. The complete MERGE statement looks like this:
The *----------* clause can be used in an *INSERT* statement to return information from the inserted rows.
The *OUTPUT* clause can be used in an *INSERT* statement to return information from the inserted rows.
Write a query that will insert into the *Sales.MyOrders* table the result of a query against *Sales.Orders* table returning orders shipped to customers in *Norway*. Columns to return from *Sales.Orders* table query: *orderid, custid, empid, orderdate, shipcountry, freight* Columns to insert into in the *Sales.MyOrders* table: *orderid, custid, empid, orderdate, shipcountry, freight* *Hint:* Don't forget to turn on the IDENTITY_INSERT option ON and then OFF when you are done.
The INSERT SELECT statement inserts the result set returned by a query into the specified target table. As with INSERT VALUES, the INSERT SELECT statement supports optionally specifying the target column names. Also, you can omit columns that get their values automatically from an identity property, default constraint, or when allowing NULLs. As an example, the following code inserts into the Sales.MyOrders table the result of a query against Sales.Orders returning orders shipped to customers in Norway. The code turns on the IDENTITY_INSERT option against Sales.MyOrders in order to use the original order IDs and not let the identity property generate those. Setting IDENTITY_INSERT to OFF causes the current identity value of the table to be set to the current maximum value in the identity column. In our example, the current identity value was set to 11015. If you now add another row to the table, the order ID will be set to 11016.
Write a query that will return orders shipped to Norway from the Sales.Orders table, create a target table called *Sales.MyOrders*, and stores the query's result in the target table. Columns to be returned and stored in a new table: *orderid* - without the identity property and NOT NULL (null values are replaced with *-1*), *custid* - NOT NULL (null values are replaced with *-1*), *empid* - remains the same, *orderdate* - cast as date and NOT NULL (null values are replaced with *'19000101'*), *shipcountry* - remains the same, *freight* - remains the same. Also, don't forget to add back the constraint on *orderid* as the *primary key*.
The SELECT INTO statement involves a query (the SELECT part) and a target table (the INTO part). The statement creates the target table based on the definition of the source and inserts the result rows from the query into that table. The statement copies from the source some aspects of the data definition like the column names, types, nullability, and identity property, in addition to the data itself. Certain aspects of the data definition aren't copied like indexes, constraints, triggers, permissions, and others. If you want to include these aspects, you need to script them from the source and apply them to the target. The following code shows an example for a SELECT INTO statement that queries the Sales. Orders table returning orders shipped to Norway, creates a target table called Sales.MyOrders, and stores the query's result in the target table: As mentioned, the SELECT INTO statement creates the target table based on the definition of the source. You don't have direct control over the definition of the target. If you want target columns to be defined different than the source, you need to apply some manipulation. For example, the source orderid column has an identity property, and hence the target column is defined with an identity property as well. If you want the target column not to have the property, you need to apply some kind of manipulation, like orderid + 0 AS orderid. Note that after you apply manipulation, the target column definition allows NULLs. If you want the target column to be defined as not allowing NULLs, you need to use the ISNULL function, returning a non-NULL value in case the source is a NULL. This is just an artificial expression that lets SQL Server know that the outcome cannot be NULL and, hence, the column can be defined as not enabling NULLs. For example, you could use an expression such as this one: ISNULL(orderid + 0, -1) AS orderid. Similarly, the source custid column is defined in the source as allowing NULLs. To make the target column be defined as NOT NULL, use the expression ISNULL(custid, -1) AS custid. If you want the target column's type to be different than the source, you can use the CAST or CONVERT functions. But remember that in such a case, the target column definition enables NULLs even if the source column disallowed NULLs, because you applied manipulation to the source column. As with the previous examples, you can use the ISNULL function to make SQL Server define the target column as not enabling NULLs. For example, to convert the orderdate column from its source type DATETIME to DATE in the target, and disallow NULLs, use the expression ISNULL(CAST(orderdate AS DATE), '19000101') AS orderdate. To put it all together, the following code uses a query similar to the previous example, only defining the orderid column without the identity property as NOT NULL, the custid column as NOT NULL, and the orderdate column as DATE NOT NULL: Remember that SELECT INTO does not copy constraints from the source table, so if you need those, it's your responsibility to define them in the target. For example, the following code defines a primary key constraint in the target table: One of the benefits of using SELECT INTO is that when the database's recovery model is not set to full, but instead to either simple or bulk logged, the statement uses an optimized logging mode. This can potentially result in a faster insert compared to when full logging is used. The SELECT INTO statement also has drawbacks. One of them is that you have only limited control over the definition of the target table. Earlier in this lesson, you reviewed how to control the definition of the target columns indirectly. But some things you simply cannot control—for example the filegroup of the target table. Also, remember that SELECT INTO involves both creating a table and populating it with data. This means that both the metadata related to the target table and the data are exclusively locked until the SELECT INTO transaction finishes. As a result, you can run into blocking situations due to conflicts related to both data and metadata access.
When does the attempt to *drop* the column fail?
The attempt to drop the column fails when the column: ■■ Is used in an index. ■■ Is used in a default, check, foreign key, unique, or primary key constraint. ■■ Is bound to a default object or a rule. In order to drop the column, you need to drop the constraint first.
Write a query that will insert orders shipped to *Norway* into the Sales.MyOrders table. You are not going to use the original order IDs in the target rows; instead, let the sequence object generate those for you. But you need to get back information from the INSERT statement about which order IDs were generated, plus additional columns from the inserted rows. Store the output result in a table instead of returning it back to the caller. Table to insert into: *Sales.MyOrders*, Columns to insert into: *custid, empid, orderdate*, Columns to output: *orderid, custid, empid, orderdate*, Table to query: *Sales.Orders*, Column to filter on: *shipcountry*, Table to store the output result in: *Sales.MyOrdersOutput*.
The design of the OUTPUT clause is very similar to that of the SELECT clause in the sense that you can specify expressions and assign them with result column aliases. One difference from the SELECT clause is that, in the OUTPUT clause, when you refer to columns from the modified rows, you need to prefix the column names with the keywords inserted or deleted. Use the prefix inserted when the rows are inserted rows and the prefix deleted when they are deleted rows. In an UPDATE statement, inserted represents the state of the rows after the update and deleted represents the state before the update. You can have the OUTPUT clause return a result set back to the caller much like a SELECT does. Or you can add an INTO clause to direct the output rows into a target table. In fact, you can have two OUTPUT clauses if you like—the first with INTO directing the rows into a table, and the second without INTO, returning a result set from the query. If you do use the INTO clause, the target table cannot participate in either side of a foreign key relationship and cannot have triggers defined on it. The OUTPUT clause can be used in an INSERT statement to return information from the inserted rows. An example for a practical use case is when you have a multi-row INSERT statement that generates new keys by using the identity property or a sequence, and you need to know which new keys were generated. For example, suppose that you need to query the Sales.Orders table and insert orders shipped to Norway to the Sales.MyOrders table. You are not going to use the original order IDs in the target rows; instead, let the sequence object generate those for you. But you need to get back information from the INSERT statement about which order IDs were generated, plus additional columns from the inserted rows. To achieve this, simply add an OUTPUT clause to the INSERT statement right before the query. List the columns that you need to return from the inserted rows and prefix them with the keyword inserted, as follows: You can see that the sequence object generated the order IDs 1 through 6 for the new rows. If you need to store the result in a table instead of returning it back to the caller, add an INTO clause with an existing target table name as follows: In an INSERT statement you're not allowed to use the deleted prefix given how there are no deleted rows.
Create a stored procedure called *Sales.OrdersForCountry*, which accepts a ship country as input (variable *@country*) and returns orders shipped to the input country. Table to be used: *Sales.Orders* Columns to be returned: *orderid, custid, empid, orderdate, shipcountry, freight* Now invoke this stored procedure with *Portugal* as the input country, and insert the result of the procedure into the *Sales.MyOrders* table. *Hint:* Use the same columns as above and don't forget to turn the *IDENTITY_INSERT* option against the target table *Sales.MyOrders ON* and *OFF* when you are finished.
With the INSERT EXEC statement, you can insert the result set (or sets) returned by a dynamic batch or a stored procedure into the specified target table. Much like the INSERT VALUES and INSERT SELECT statements, INSERT EXEC supports specifying an optional target column list, and allows omitting columns that accept their values automatically. To demonstrate the INSERT EXEC statement, the following example uses a procedure called Sales.OrdersForCountry, which accepts a ship country as input and returns orders shipped to the input country. Run the following code to create the Sales.OrdersForCountry procedure: Run the following code to invoke the stored procedure with Portugal as the input country, and insert the result of the procedure into the Sales.MyOrders table: Here as well, the code turns on the IDENTITY_INSERT option against the target table so that the INSERT statement can specify the values for the identity column instead of letting the property assign those. INSERT EXEC works even when the source dynamic batch or stored procedure has more than one query. But that's as long as all queries return result sets that are compatible with the target table definition.
Insert these rows into the *Sales.MyOrders* table. Columns to be inserted into: *custid, empid, orderdate, shipcountry, freight* Data to be inserted: (2, 19, '20170620', N'USA', 30.00), (3, 11, DEFAULT, N'USA', 10.00), (3, 17, DEFAULT, N'USA', 30.00), (2, 11, '20170620', N'USA', 50.00), (5, 13, '20170620', N'USA', 40.00), (7, 17, '20170620', N'USA', 45.00).
With the INSERT VALUES statement, you can insert one or more rows into a target table based on value expressions. Here's an example for a statement inserting four rows into the Sales.MyOrderValues table. The INSERT VALUES statement does not specify a value for a column with an identity property because the property generates the value for the column automatically. Observe that the previous statement doesn't specify the orderid column. If you do want to provide your own value instead of letting the identity property do it for you, you need to first turn on a session option called IDENTITY_INSERT, as follows: SET IDENTITY_INSERT <table> ON; When you're done, you need to remember to turn it off like so: SET IDENTITY_INSERT <table> OFF; Note that in order to use this option, you need quite strong permissions; you need to be the owner of the table or have ALTER permissions on the table. Besides using the identity property, there are other ways for a column to get its value automatically in an INSERT statement. A column can have a default constraint associated with it like the orderdate column in the Sales.MyOrders table. If the INSERT statement doesn't specify a value for the column explicitly, SQL Server will use the default expression to generate that value. For example, the second row doesn't specify a value for orderdate, and therefore SQL Server uses the default expression: current system date and time formatted as date. Another way to achieve the same behavior is to specify the column name in the names list and the keyword DEFAULT in the respective element in the VALUES list as seen in the third row. If you don't specify a value for a column, SQL Server first checks whether the column gets its value automatically—for example, from an identity property or a default constraint. If that's not the case, SQL Server checks whether the column allows NULLs, in which case it assumes a NULL. If that's not the case, SQL Server generates an error. The INSERT VALUES statement doesn't limit you to inserting only one row; rather, it enables you to insert multiple rows. Simply separate the rows with commas, as seen in this example. Note that the entire statement is considered one transaction, meaning that if any row fails to enter the target table, the entire statement fails and no row is inserted. Remember that some of the INSERT examples relied on the default expression associated with the orderdate column, so naturally the dates you get reflect the date when you ran those examples.
Write a query that will split your *large delete statement* into more manageable chunks of 1000 rows deleted one at a time. Delete only those rows whose *product ID is 12*. Also, insert a conditional statement that checks whether the number of deleted rows is less than 1000. If this is the case, then break (exit) the while loop and end the loop. *Hint:* Use an *infinite while loop*. Table to be deleted from: *Sales.MyOrderDetails*, Column to filtered on: *productid*
You can achieve this by using a DELETE statement with a TOP option that limits the number of affected rows in a loop. Here's an example for implementing such a solution: As you can see, the code uses an infinite loop (WHILE 1 = 1 is always true). In each iteration, a DELETE statement with a TOP option limits the number of affected rows to no more than 1,000 at a time. Then the IF statement checks if the number of affected rows is less than 1,000; in such a case, the last iteration deleted the last chunk of qualifying rows. After the last chunk of rows has been deleted, the code breaks from the loop. With this sample data, there are only 14 qualifying rows in total. So if you run this code, it is done after one round, break from the loop, and return. But with a large number of qualifying rows, say, millions, you'd very likely be better off with such a solution.
You can use the *----------* clause to return information from *deleted rows* in a *DELETE* statement.
You can use the *OUTPUT* clause to return information from *deleted rows* in a *DELETE* statement.
You can use the *----------* clause to return information from modified rows in an *UPDATE* statement.
You can use the *OUTPUT* clause to return information from modified rows in an *UPDATE* statement.
Write a query that will delete the rows from the *Sales.MyOrders* table where the *employee ID is equal to 1*. Table to delete from: *Sales.MyOrders*, Column to output: *orderid*, Column to filter on: *empid*.
You can use the OUTPUT clause to return information from deleted rows in a DELETE statement. You need to prefix the columns that you refer to with the keyword deleted. In a DELETE statement you're not allowed to use the inserted prefix given that there are no inserted rows. The following example deletes the rows from the Sales.MyOrders table where the employee ID is equal to 1. Using the OUTPUT clause, the code returns the order IDs of the deleted orders:
