Advanced Insertion and Deletion
Traditionally with SQL, INSERT and DELETE statements work with only one row at time. For example, if you have ten rows to insert in a table, you have to do ten INSERT statements, usually in a loop. This can be very inefficient.
Insertion with SELECT
Standard insertion is very simple. For example, let’s consider the case where you want to add a product to an order. To simplify error checking, we will create a stored procedure called sp_OrderAddProduct.
Listing D-1
 |
/******************************************
Drop stored procedure if already exist
******************************************/
if exists (select * from dbo.sysobjects where id =
object_id(N'sp_OrderAddProduct') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure sp_OrderAddProduct
GO
create procedure sp_OrderAddProduct
/******************************************
Procedure: sp_OrderAddProduct
Date: 14 July 2002
Author: Ryan N. Payet
Description: Insert New products in [Order Details] table
Version History
---------------
0.1, 14 July 2002, Ryan N. Payet:
Procedure Created
******************************************/
@OrderID integer,
@ProductID integer,
@Quantity smallint = 1,
@Discount real = 0
as
BEGIN
DECLARE @@UnitPrice as Money
DECLARE @@ERRORMSG as varchar(256)
/*Check Order exist */
IF NOT EXISTS (SELECT OrderID FROM Orders WHERE OrderID
= @OrderID )
BEGIN
/*Order does not exist, raise error*/
SET @@ERRORMSG = 'Given Order does not exist in table
Orders'
RAISERROR ( @@ERRORMSG, 16, 1)
RETURN 1
END
/*Check Valid Product exist */
IF NOT EXISTS
(
SELECT ProductID FROM Products
WHERE ProductID = @ProductID AND
Discontinued = 0
)
BEGIN
/*Order does not exist, return with 1*/
SET @@ERRORMSG = 'Given Product does not exist in table
Products or it has been discontinued'
RAISERROR ( @@ERRORMSG , 16, 1)
RETURN 1
END
SELECT @@UnitPrice = UnitPrice
FROM Products
WHERE ProductID = @ProductID
/* Do the insertion */
BEGIN TRANSACTION
INSERT INTO [Order Details]
(OrderID, ProductID, UnitPrice, Quantity, Discount)
VALUES
( @OrderID, @ProductID, @@UnitPrice, @Quantity,
@Discount)
IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
RETURN 0
END
GO
|
| Tip |
Notice that there is a header comment within the CREATE statement that describes the functionality and version history of stored procedures. This allows the comments to be stored together with the procedure in the database so that future developers will be able to see the comment, even though they might not have the source script.
|
The stored procedure is simple and contains some error checking routines. This error checking helps to maintain data integrity, such as preventing addition of discontinued products, and avoid getting constraint violation errors with foreign keys. To do error checking and get the current unit price of a product requires three SELECT statements. Now consider the following listing, which makes use of INSERT with SELECT.
Listing D-2
|
/******************************************
Drop stored procedure if already exist
******************************************/
if exists (select * from dbo.sysobjects where id =
object_id(N'sp_OrderAddProduct') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure sp_OrderAddProduct
GO
create procedure sp_OrderAddProduct
/******************************************
Procedure: sp_OrderAddProduct
Date: 14 July 2002
Author: Ryan N. Payet
Description: Insert New products in [Order Details] table
Version History
---------------
0.1, 14 July 2002, Ryan N. Payet:
Procedure Created
0.2, 14 July 2002, Ryan N. Payet:
Optimized to use INSERT with SELECT
******************************************/
@OrderID integer,
@ProductID integer,
@Quantity smallint = 1,
@Discount real = 0
as
BEGIN
DECLARE @@ERRORMSG as varchar(256)
/* Do the insertion */
BEGIN TRANSACTION
INSERT INTO [Order Details]
(OrderID, ProductID, UnitPrice, Quantity, Discount)
SELECT Orders.OrderID, Products.ProductID,
Products.UnitPrice,1,0
FROM Orders, Products
WHERE Orders.OrderID = @OrderID AND
Products.ProductID = @ProductID AND
Products.Discontinued = 0
/*
Check to see if 1 row was affected
If no row or more than 1 row was inserted
then there was error
*/
IF @@ROWCOUNT <> 1
BEGIN
SET @@ERRORMSG = 'Given Order or Product does not
exist or the Product has been
discontinued'
RAISERROR ( @@ERRORMSG, 16, 1)
ROLLBACK TRANSACTION
RETURN 1
END
ELSE
BEGIN
COMMIT TRANSACTION
RETURN 0
END
END
GO
|
Version 0.2 of the stored procedure is smaller, and the INSERT statement now uses a SELECT to generate the data it requires for the insert. This is much more efficient than the previous version, which required three SELECT statements and one INSERT. The main rule to remember is that the columns returned by the SELECT statement must be compatible with the column list in the INSERT INTO section. Another variation of this to use a stored procedure that returns Recordset.
INSERT INTO <Table_Name>
(<Column_List>)
EXECUTE <Stored_Procedure>
The INSERT with SELECT really excels when there is more than one row of data that you want to insert. Suppose you want to copy the order details of one order to another. With the standard INSERT, you will have to loop through the source and do an insert for each row. Using INSERT with SELECT, only one INSERT statement is required.
Listing D-3
DECLARE @@SrcOrderID as integer
DECLARE @@DestOrderID as integer
SET @@SrcOrderID = 10248
SET @@DestOrderID = 10296
INSERT INTO [Order Details]
(OrderID, ProductID, UnitPrice, Quantity, Discount)
SELECT @@DestOrderID,
[Order Details].ProductID,
[Order Details].UnitPrice,
[Order Details].Quantity,
[Order Details].Discount
FROM [Order Details]
WHERE [Order Details].OrderID = @@SrcOrderID AND
[Order Details].ProductID NOT IN
( SELECT ProductID FROM [Order Details]
WHERE [Order Details].OrderID = @@DestOrderID
)
In the example in Listing D-3, the order details of order 10248 are copied to order 10296. If you look carefully at the SELECT statement, you will notice that it includes a sub-SELECT in the WHERE clause to prevent copying of products that are already available in order 10296.
DELETE with Multiple Table WHERE Clause
We have so far seen how we can insert multiple-row data using SELECT. With DELETE, you have a WHERE clause, so it is possible to delete more than one row of data. You are probably wondering when you would need to refer to multiple tables. Consider the following problem. Suppose you want to delete discontinued products from the Order Details table that has been maintained by a particular employee. The relationship between the Orders, Order Details, and Products tables are shown in Figure D-1.
To solve the problem, let’s approach it from a SELECT angle, which is “Show discontinued Products from Order Details that has been maintained by a particular employee.” Using the relationship, we can do a simple SELECT, as shown here:
Listing D-4
SELECT [Order Details].OrderID,
[Order Details].ProductID
FROM [Order Details]
INNER JOIN Products
ON [Order Details].ProductID = Products.ProductID
INNER JOIN Orders
ON Orders.OrderID = [Order Details].OrderID
WHERE (Products.Discontinued = 1) AND
(Orders.EmployeeID = 1)
| Note |
For the example, we will use the employee with EmployeeID 1. This can be replaced later by the required EmployeeID. Discontinued products have the Products.Discontinued value of 1.
|
The result of the SELECT is the rows that we want to delete. By doing the SELECT first, we are able to see exactly which row will be deleted. Now it is a simple matter of modifying the script to make it do the deletion.
Listing D-5
DELETE [Order Details]
FROM [Order Details]
INNER JOIN Products
ON [Order Details].ProductID = Products.ProductID
INNER JOIN Orders
ON Orders.OrderID = [Order Details].OrderID
WHERE (Products.Discontinued = 1) AND
(Orders.EmployeeID = 1)
| Warning |
Running the script will cause certain data to be deleted. It is recommended that you back up the Northwind database before you run the script. You can then restore it later when you have finished with this appendix.
|
One additional point about DELETE is that if the table is used in a sub-query, you must use an alias. For example, if you want to delete the five lowest performing Order Details, you would have to use the following script:
Listing D-6
DELETE [Order Details]
FROM
( SELECT TOP 5 OrderID,
SUM(UnitPrice * Quantity * (1 - Discount )) as
Ordervalue
FROM [Order Details] GROUP BY OrderID
ORDER BY Ordervalue
) as T1
WHERE [Order Details].OrderID = T1.OrderID
As you can see, you can even use TOP, GROUP BY, and ORDER BY in the sub-SELECT.
