|
|
Posted by Erland Sommarskog on 05/02/07 21:36
eighthman11 (rdshultz@nooter.com) writes:
> Using Sql Server SQL 8
> I'm trying to INSERT records into a "can software package" batch
> table. I have a work-table that mimics the batch table. After
> manipulating the records in the work-file I want to INSERT them into
> the batch table.
>
> The problem is the batch table in the can software has a trigger on
> the batch table which is going to force me to INSERT one record at a
> time. I've always been able to do an INSERT with no problem.
Ouch! Apparently someone did not know how to write a set-based trigger.
> The batch table has pretty basic columns:
> BatchID
> BatchDate
> SeqNumber
> These three fields are the key and then just some miscellaneous
> columns. Any easy way to loop thru my work-file to insert these
> records. Never done a loop in SQL so an example would be really
> really appreciated. I have a sequence number so I was hoping to do a
> While loop but I really don't know enough about creating a loop to
> make that call. Thanks in advance for any help.
They say cursors are evil, and that's true: iterative solutions are
almost always magnitudes slower than set-based. But when you need to
iterate, cursors is usually the best solution.
DECLARE @batchid int,
@batchdate datetime,
@seqnumber int,
....
DECLARE cur CURSOR STATIC LOCAL FOR
SELECT batchid, batchdate, seqnumer, ....
FROM yourworktable
-- ORDER BY if you like
OPEN cur
WHILE 1 = 1
BEGIN
FETCH cur INTO @batchid, @batchdate, @seqnumer, ...
IF @@fetch_status <> 0
BREAK
INSERT targettable (...)
VALUES (....)
END
DEALLOCATE cur
Notes:
DECLARE CURSOR - creates the cursor.
STATIC - the result set for the cursor is defined once for all into
tempdb.
LOCAL - Cursor is visible in current scope only.
OPEN - Opens the cursor.
FETCH - get next from the cursor.
@@fetch_status - 0 as long as there are more rows in the pipeline.
DEALLOCATE - deletes the cursor.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|