7.5 Importing with a Single Procedure

7.5.1 Problem

The previous recipe requires a call to the import procedure after each row is inserted into the buffer table. You have a large number of rows that you need to insert simultaneously. You would like to load all your data into the buffer table and then make just one procedure call to move that data into your live table.

7.5.2 Solution

The solution is to design a stored procedure that opens a cursor on the buffer table to retrieve and process all the newly imported rows. As in the previous recipe, newly imported rows are marked with a status value of 'I'.

The following procedure is named ImportFull. It continues with the Bookstore example introduced in the previous recipe. All newly imported rows are fetched via the cursor named BufferCursor. Each of these rows is validated. Rows that pass the validation tests are loaded into the live production table. Rows that fail validation are rejected and their status is changed to R.

CREATE PROCEDURE ImportFull 
AS BEGIN 
   DECLARE @id UNIQUEIDENTIFIER
   
   DECLARE @BookId INTEGER
   DECLARE @Name CHAR(40)
   DECLARE @Quantity INTEGER
   DECLARE @Price DECIMAL(10,2)
   DECLARE @Type CHAR(20)

   DECLARE @CheckFlag INTEGER

   DECLARE BufferCursor CURSOR FOR
      SELECT Id, BookId, Name, Quantity, Price, Type 
      FROM BookstoreBuffer WHERE Status='I'
   
   OPEN BufferCursor

   FETCH NEXT FROM BufferCursor 
      INTO @Id, @BookId, @Name, @Quantity, @Price, @Type

   WHILE @@FETCH_STATUS=0
   BEGIN
      /* Place validation code here. Set @checkFlag to 0
         for good records, and set it to 1 for bad records.
      */
      IF @Quantity < 0
         SELECT @CheckFlag=1
      ELSE 
         SELECT @CheckFlag=0
      
      /* If the row passed validation, update the operational table.
      */
      IF @checkFlag=0 BEGIN
         UPDATE Bookstore 
         SET 
            Name=@Name,
            Quantity=@Quantity,
            Price=@Price,
            Type=@Type
         WHERE bookID=@bookId
         IF @@ROWCOUNT=0  
            INSERT INTO Bookstore(BookId, Name, Quantity, Price, Type)
               VALUES(@BookId, @Name, @Quantity, @Price, @Type)
         UPDATE BookstoreBuffer SET Status='A' WHERE Id=@Id
      END ELSE 
         UPDATE BookstoreBuffer SET Status='R' WHERE Id=@Id

      FETCH NEXT FROM BufferCursor 
         INTO @Id, @BookId, @Name, @Quantity, @Price, @Type
   END

   CLOSE BufferCursor
   DEALLOCATE BufferCursor
END

7.5.3 Discussion

The ImportFull procedure shown here uses a procedural code and a cursor to step through the rows of the buffer table. This is efficient, and it gives you a chance to look at each row separately. Good rows are inserted into the production table. Bad rows are marked as rejects so that they can be identified and fixed at a later time.