Joe!
This does exactly what I need... Does the fn_split() use a while loop to
parse the list and populate the table variable or another more efficient
method? It looks like you pass it the list of values and the delimiter to
fn_split()... then it populates (loops?) and returns a one column (column
named "value") table variable which you can name in the select portion of
the INSERT SELECT statement....
Question now is how to efficiently populate the table_varible....
Thanks,
Orlando
-----Original Message-----
From: Cruz, Joseph [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 23, 2001 8:27 AM
To: SQL
Subject: RE: Inserting Multiple Rows in with one INSERT Statement
-----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
