Roll this into a stored proceedure and pass your list to that....
Change the insert queries to match your database and rem out the print
statements...
=============================================================
BEGIN STORED PROCEEDURE
=============================================================
CREATE PROCEEDURE usp_InsertList
@myList nvarchar(255) -- this is the list!!!
@strIndex int
@findIndex int
@theName nvarchar(255)
--rem out the set list once you are passing the variable...
SET @mylist = 'joe,john,patty,bob'
SET @strIndex = 1
SET @findIndex = 1
WHILE (LEN(@myList) <> 0)
BEGIN
SET @findIndex = CHARINDEX(',', @mylist, @strIndex)
PRINT 'Found a Comma at: ' + Convert(nvarchar(10), @findIndex)
-- Deal with lists that have one member
IF @findIndex = 0 -- single value list
BEGIN
SET @theName = @myList
PRINT 'This is the Final List Item: ' + @myList
--INSERT INTO mytable (myfield) VALUES (@theName)
BREAK
END
--otherwise there are still elements...
ELSE
BEGIN
SET @theName = SUBSTRING(@mylist, @strIndex, (@findIndex - @strIndex))
PRINT 'This is the name we found ' + @theName
--INSERT INTO mytable (myfield) VALUES (@theName)
SET @mylist = RIGHT(@mylist, ((LEN(@mylist) - (@findIndex -
@strIndex))-1))
PRINT
'======================================================================='
END
END
Jeff Garza
Lead Developer/Webmaster
Spectrum Astro, Inc.
480.892.8200
[EMAIL PROTECTED]
http://www.spectrumastro.com
-----Original Message-----
From: Thanh Nguyen [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 04, 2002 2:38 PM
To: CF-Talk
Subject: RE: Insert a list into a table - help!!!
MSSQL
-----Original Message-----
From: Garza, Jeff [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 04, 2002 12:24 PM
To: CF-Talk
Subject: RE: Insert a list into a table - help!!!
What database are you using?
Jeff Garza
Lead Developer/Webmaster
Spectrum Astro, Inc.
480.892.8200
[EMAIL PROTECTED]
http://www.spectrumastro.com
-----Original Message-----
From: Thanh Nguyen [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 04, 2002 1:20 PM
To: CF-Talk
Subject: Insert a list into a table - help!!!
I have a list with at least 3 items. And I want to insert it into
table.
right now, I'm looping through the list to insert them into db, but
this is
like writing multiple cfquery's. and what happens if my list has 20
items.
<cfloop index="index" list="#mylist#>
<cfquery >
insert into my_table
(id, itemName)
values
(#ID#, '#index#' )
</cfquery>
</cfloop>
Is there a better way to do this?
______________________________________________________________________
Get Your Own Dedicated Windows 2000 Server
PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
