Brian,
As far as I can tell there are two ways to operate on data: on a row/record
basis OR as sets. Will you be looping through your data ? Well, it depends
on your specific situation.
Situation 1
------------
If you are trying to enter multiple rows of free form text in the database,
then yes you are probably going to be looping over an array and following a
very similar logic path to the CF one you are trying to avoid. However, the
reason you are trying to avoid looping over a dataset and running multiple
inserts is because the connection process to a database is "heavy". So,
even though you might still be looping over data in the DB you might
actually be increasing performance. This depends on the specific factors in
your application....for example if the DB is running at a high utilization
.....maybe this method isn't for you. If your middleware / webserver(s) are
being taxed you might want to consider this path. The string parsing
efficiency of your database server versus the string parsing efficiency of
CF & connection overhead must also be factored into this equation.
I found the need to do this a while back I just can't find the sample code I
wrote. But you'll just pass in the appropriate strings that are value
delimited. Don't forget to encode the data in the strings if their is a
possibility of having the delimiter character in the data. Once you have
the string in the DB you'll need to loop over the string(s) and parse out
the nuggets of data, then run your INSERT (or whatever).
Obviously you don't want to avoid turning your database into a row based
data engine except where necessary. If you can find a way to work situation
2 ...go with it over situation 1
Situation 2
--------------------
If you are trying to insert data that is coming from other tables that
already exist most likely you can pass in some strings and some clever
INSERT INTO / SELECT FROM statements. When I say data that already exists,
take for example a College that has Students and Classes. If you want to
associate a student with an arbitrary number of classes You could do
something like the following :
CREATE PROCEDURE addClasses
@StudentID INT = 1,
@classesSelected VarChar(255) = NULL,
EXEC(
"INSERT INTO StudentClasses (StudentID,ClassID)
SELECT StudentID = " + @studentID + ", ClassID
FROM Classes
WHERE ClassID IN (" + @classesSelected + ")"
)
GO
The above route is SQL Server code, but I'm sure PL/SQL has some similar
capabilities. If you can go this route you will avoid looping ...you will be
operating on your data in a set based way.
Hope this helps.
-eric
------------------------------------------------
If you don't read news.groups, the net appears to be a rather tranquil
place.
-- Karl Lehenbauer, about Usenet
Eric Barr
Zeff Design
(p) 212.714.6390
(f) 212.580.7181
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 19, 2000 1:30 PM
To: CF-Talk
Subject: RE: Bulk Insert?
Ok, you have me intrigued. Are you talking about a PL/SQL procedure that
takes the entire array and then parses it? Won't you be looping through the
array anyway?
Brian
-----Original Message-----
From: Eric Barr [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 19, 2000 11:31 AM
To: CF-Talk
Subject: RE: Bulk Insert?
Jon,
You can get away with this in some situations through some clever gymnastics
in your sql.
What it will come down to is passing a string into a stored procedure that
you then do some string parsing and looping and use EXEC() (assuming your
using SQL server). Althouhg procedding in this manor does impose some
constraints: a lot of string manipulation in SQL is generally a bad idea,
limit to the length of the string that can be passed in, etc...... But, I
would try it out both ways and run some informal performance tests to figure
out which way fits your specific situation.
-eric
------------------------------------------------
If to please people we offer what we ourselves disapprove, how can we
afterwards defend our work.
-George Washington
Eric Barr
Zeff Design
(p) 212.714.6390
(f) 212.580.7181
-----Original Message-----
From: Jon Hall [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 18, 2000 9:23 PM
To: CF-Talk
Subject: Bulk Insert?
I am wondering if anyone knows of any way to dump the entire contents of an
array into a table without having to loop over an insert query?
jon
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
