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. 6 10940
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
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
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.
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
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: george |
last post by:
This is like the bug from hell. It is kind of hard to explain, so
please bear with me.
Background Info: SQL Server 7.0, on an NT box, Active Server pages
with Javascript, using ADO objects.
...
|
by: Philip Boonzaaier |
last post by:
I want to be able to generate SQL statements that will go through a list of
data, effectively row by row, enquire on the database if this exists in the
selected table- If it exists, then the colums...
|
by: ms |
last post by:
I am running an insert statement from a dbf file and there is one record causing
the insert to fail. A msg. is returned stating it is due to an invalid
datatype. There are 2 text fields, 2...
|
by: Bri |
last post by:
Greetings,
I'm having a very strange problem in an AC97 MDB with ODBC Linked tables
to SQL Server 7. The table has an Identity field and a Timestamp field.
The problem is that when a new record...
|
by: Carl |
last post by:
Hi,
I hope someone can share some of their professional advice and help me out
with my embarissing problem concerning an Access INSERT query. I have never
attempted to create a table with...
| |
by: SandySears |
last post by:
I am trying to use a stored procedure to insert a record using VS 2005, VB
and SQL Server Express. The code runs without errors or exceptions, and
returns the new identifer in the output...
|
by: rn5a |
last post by:
During registration, users are supposed to enter the following
details: First Name, Last Name, EMail, UserName, Password, Confirm
Password, Address, City, State, Country, Zip & Phone Number. I am...
|
by: djsdaddy |
last post by:
Good Day All, I have some EEO data in an old dBase4 database that I have converted to an Access table. Since dBase was not a relational database, I didn't create any key fields. I linked all of the...
|
by: MLH |
last post by:
Suppose, in a multi-user environment, you
have append query SQL in a VBA procedure
that looks like INSERT INTO MyTable...
and the next line reads MyVar=DMax("","MyTable...
You can never be...
|
by: magnolia |
last post by:
i created a trigger that will record the changes made to a table .everything works fine except the insert query.whenerever i try to insert a record it fires insert and update triger at the same time...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |