-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
It looks like a stored procedure is in order. I'm not sure how much you've
dealt with them in SQL 2000, but one of the niftier features in SQL 2000 is
User-Defined Functions!
What you might want to attempt to do in your case is pass two values into a
stored procedure, the first being the uid, and then the other being a
comma-delimited list of interest ids.
Something like:
<cfquery ... >
EXEC usp_insert_user_interests @uid=#uid#, @interest_list = '1,17,15,26,33,14'
</cfquery>
In the stored proc, you could use the fantastically functional fn_split()
function (check out http://www.sqlmag.com/Articles/Index.cfm?ArticleID=21071
available to subscribers only for now, but maybe you already subscribe?) to
return a table that you can join your results to:
CREATE PROC usp_insert_user_interests
(
@uid integer,
@interest_list varchar(500)
)
INSERT user_interest
SELECT @uid uid, split_table.value
FROM dbo.fn_split(@interest_list,',') as split_table
RETURN
Hope that helps!
:)
Joe
============================================
Joseph Cruz
Programmer/Analyst
3620 Locust Walk, suite 400
Philadelphia, PA 19104-6302
215-898-1220 (work)
215-308-0657 (pager)
215-768-2071 (cell)
[EMAIL PROTECTED]
============================================
> -----Original Message-----
> From: Correa, Orlando (ITSC) [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, August 23, 2001 8:51 AM
> To: SQL
> Subject: Inserting Multiple Rows in with one INSERT Statement
>
>
>
>
> I have the following query which inserts multiple rows based
> on a users choices in an HTML form as a batch... The batch
> is create by looping over the prepared statement and adding
> the values, then sending the whole enchilada to the db (SQL
> Server 2000) at once... Is there a better/faster/more
> efficient way to do this...? I'd like to pass a block of
> values at once via an array or structure to insert multiple
> rows at once...?
>
>
> <CFIF listlen(interest_ids)>
> <CFQUERY NAME="#queryname#" DATASOURCE="#ds#"
> USERNAME="#dbun#" PASSWORD="#dbps#" DBTYPE="#dbtype#">
> <CFLOOP list="#interest_ids#" index="interest_id">
> INSERT INTO user_interest
> (uid,interest_id)
> VALUES
> (<CFQUERYPARAM cfsqltype="CF_SQL_INTEGER" value="#uid#">,
> <CFQUERYPARAM cfsqltype="CF_SQL_INTEGER"
> value="#interest_id#">);
> </CFLOOP>
> </CFQUERY>
> </CFIF>
>
>
> qry_user_interest_insert (Records=0, Time=50ms)
> SQL =
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
> INSERT INTO sfnm_dev.dbo.user_interest
> (uid,interest_id) VALUES (?,?);
>
> Query Parameter Value(s) -
> Parameter #1 = 1
> Parameter #2 = 17
> Parameter #3 = 1
> Parameter #4 = 1
> Parameter #5 = 1
> Parameter #6 = 2
> Parameter #7 = 1
> Parameter #8 = 3
> Parameter #9 = 1
> Parameter #10 = 26
> Parameter #11 = 1
> Parameter #12 = 18
> Parameter #13 = 1
> Parameter #14 = 19
> Parameter #15 = 1
> Parameter #16 = 20
> Parameter #17 = 1
> Parameter #18 = 4
> Parameter #19 = 1
> Parameter #20 = 32
> Parameter #21 = 1
> Parameter #22 = 5
> Parameter #23 = 1
> Parameter #24 = 36
> Parameter #25 = 1
> Parameter #26 = 21
> Parameter #27 = 1
> Parameter #28 = 6
> Parameter #29 = 1
> Parameter #30 = 22
> Parameter #31 = 1
> Parameter #32 = 23
> Parameter #33 = 1
> Parameter #34 = 24
> Parameter #35 = 1
> Parameter #36 = 7
> Parameter #37 = 1
> Parameter #38 = 8
> Parameter #39 = 1
> Parameter #40 = 9
> Parameter #41 = 1
> Parameter #42 = 10
> Parameter #43 = 1
> Parameter #44 = 25
> Parameter #45 = 1
> Parameter #46 = 11
> Parameter #47 = 1
> Parameter #48 = 12
> Parameter #49 = 1
> Parameter #50 = 35
> Parameter #51 = 1
> Parameter #52 = 27
> Parameter #53 = 1
> Parameter #54 = 28
> Parameter #55 = 1
> Parameter #56 = 29
> Parameter #57 = 1
> Parameter #58 = 33
> Parameter #59 = 1
> Parameter #60 = 13
> Parameter #61 = 1
> Parameter #62 = 14
> Parameter #63 = 1
> Parameter #64 = 15
> Parameter #65 = 1
> Parameter #66 = 30
> Parameter #67 = 1
> Parameter #68 = 31
> Parameter #69 = 1
> Parameter #70 = 16
> Parameter #71 = 1
> Parameter #72 = 34
-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 7.0.3 for non-commercial use <http://www.pgp.com>
iQA/AwUBO4UTh9CGp5RuWHIBEQKIdACeKe5dNDECWVTojOYr66Whp8O5UYgAoIpP
BKIuAG9A3WG52gNOCnn/Q0A6
=R184
-----END PGP SIGNATURE-----
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
