|
|
Posted by eighthman11 on 05/03/07 14:05
On May 2, 4:36 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> eighthman11 (rdshu...@nooter.com) writes:
> > Using Sql Server SQL 8
> > I'm trying toINSERTrecords 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 toINSERTthem 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 toINSERTone record at a
> > time. I've always been able to do anINSERTwith 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 toinsertthese
> > 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
>
> INSERTtargettable (...)
> 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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
I would like to thank everyone for their response. I tried both
examples I was given and both worked great. Right now I am in testing
and I am only using a handful of records. Later on when I am
inserting hundreds or thousands of records I will try both ways again
to see which processes faster. Once again I really appreciate the
help.
Navigation:
[Reply to this message]
|