DekGenius.com
Team LiB
Previous Section Next Section

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
Start example
/******************************************
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
End example
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
Start example
/******************************************
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
End example

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
Start example
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
       )
End example

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.

Click To expand
Figure D-1: Orders, Order Details, and Products tables

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
Start example
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)
End example
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
Start example
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)
End example
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
Start example
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
End example

As you can see, you can even use TOP, GROUP BY, and ORDER BY in the sub-SELECT.

Team LiB
Previous Section Next Section