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
Thanks,
Orlando
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
