By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,426 Members | 1,729 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,426 IT Pros & Developers. It's quick & easy.

INSERT - one record at a time

P: n/a
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.

May 2 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On May 2, 2:09 pm, eighthman11 <rdshu...@nooter.comwrote:
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
May 2 '07 #2

P: n/a
eighthman11 (rd******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 2 '07 #3

P: n/a
On May 2, 4:36 pm, Erland Sommarskog <esq...@sommarskog.sewrote:
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.
May 3 '07 #4

P: n/a
eighthman11 (rd******@nooter.com) writes:
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.
Hundreds of thousands? I'm afraid that you will find both methods painfully
slow. With that size I would be prepared to look into do modify the trigger,
despite that it would void any warranties.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 3 '07 #5

P: n/a
On May 3, 9:19 am, Erland Sommarskog <esq...@sommarskog.sewrote:
eighthman11 (rdshu...@nooter.com) writes:
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.

Hundreds of thousands? I'm afraid that you will find both methods painfully
slow. With that size I would be prepared to look into do modify the trigger,
despite that it would void any warranties.

--
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
Sorry for the confustion. It isn't hundreds of thousands. It would
be hundreds OR thousands of records. In actuality I can't imagine
there ever being more than 3 thousand records.

May 3 '07 #6

P: n/a
eighthman11 (rd******@nooter.com) writes:
Sorry for the confustion. It isn't hundreds of thousands. It would
be hundreds OR thousands of records. In actuality I can't imagine
there ever being more than 3 thousand records.
OK, then you *may* be able to sustain the performance. But with 3000
rows to insert, you will need to have some patience.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 3 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.