Posted by Utahduck on 05/02/07 20:21

On May 2, 2:09 pm, eighthman11 <rdshu...@nooter.com> wrote:
> Hi everyone:
>
> 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.
>
> 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.

Google "SQL Cursors". Personally I hate cursors and use a WHILE loop:

DECLARE @ValueDataType

WHILE (SELECT COUNT(*) FROM Table) > 0
BEGIN

SET @Value = distinct value from Table

INSERT INTO Table2
SELECT *
FROM Table
WHERE Value = @Value

DELETE Table WHERE Value = @Value

END

Somebody can probably produce the same code using a cursor. I just
choose to ignore them. heh.

-Utah

[Back to original message]