Section 7.5. Importing with a Single Procedure

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.