RE: Mass Database update
run it all in a dts package truncate the table and then use bcp to import the csv file. -Original Message- From: Chad [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 25, 2002 10:37 AM To: CF-Talk Subject: Mass Database update We have a client that wants to drop a database table and replace it with a new one every once and a while. Is there any dummy proof ways of doing this? Have them upload a CSV, use CFFILE to loop over the information and import it? The data will start as an Excel table. __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Mass Database update
BCP? -Original Message- From: Joshua Tipton [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 25, 2002 9:38 AM To: CF-Talk Subject: RE: Mass Database update run it all in a dts package truncate the table and then use bcp to import the csv file. -Original Message- From: Chad [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 25, 2002 10:37 AM To: CF-Talk Subject: Mass Database update We have a client that wants to drop a database table and replace it with a new one every once and a while. Is there any dummy proof ways of doing this? Have them upload a CSV, use CFFILE to loop over the information and import it? The data will start as an Excel table. __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Mass Database update
You'll probably not want to tackle this with ColdFusion. Use a transformation tool native to your DB if it's truly 'massive'. This will give you the most integrity/performance. If you want to automate the process using ColdFusion, that would be fine. Have them upload the Excel spreadsheet via CFFILE, and then have CF fire a StoredProcedure on your DB to import the information properly from Excel. You could pass the Stored Procedure the excel file name and make it truly dynamic/automated. Adam. -Original Message- From: Chad [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 25, 2002 9:37 AM To: CF-Talk Subject: Mass Database update We have a client that wants to drop a database table and replace it with a new one every once and a while. Is there any dummy proof ways of doing this? Have them upload a CSV, use CFFILE to loop over the information and import it? The data will start as an Excel table. __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Mass Database update
If you're using SQL server then you can do this much more efficiently with a DTS. http://www.sqlteam.com/item.asp?ItemID=10627 HTH, -- Howie Hamlin - inFusion Project Manager On-Line Data Solutions, Inc. - www.CoolFusion.com - 631-737-4668 x101 *** Please vote for iMS here: http://www.sys-con.com/coldfusion/readerschoice2002/nominationform.cfm *** inFusion Mail Server (iMS) - The Award-winning, Intelligent Mail Server Find out how iMS Stacks up to the competition: http://www.coolfusion.com/imssecomparison.cfm - Original Message - From: Chad [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, September 25, 2002 10:36 AM Subject: Mass Database update We have a client that wants to drop a database table and replace it with a new one every once and a while. Is there any dummy proof ways of doing this? Have them upload a CSV, use CFFILE to loop over the information and import it? The data will start as an Excel table. __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Mass Database update
Chad, DTS will allow you to import from a .CSV. To dummy proof it use the column transformations tool in the package designer and write little validation routines in VB script. The little IDE makes it pretty easy. You can validate for length, numeric, date etc. (the date validation is tricky). I've always liked this approach rather than CF for file import. CF is ok if you have a limited number of rows, but long running HTTP requests are not usually the way to go - my .02. -mk -Original Message- From: Chad [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 25, 2002 9:37 AM To: CF-Talk Subject: Mass Database update We have a client that wants to drop a database table and replace it with a new one every once and a while. Is there any dummy proof ways of doing this? Have them upload a CSV, use CFFILE to loop over the information and import it? The data will start as an Excel table. __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: mass database update
# 5 needs more input... ;-) What is the relationship between count and ID and where does count get set? Bryan Stevenson VP Director of E-Commerce Development Electric Edge Systems Group Inc. p. 250.920.8830 e. [EMAIL PROTECTED] - Macromedia Associate Partner www.macromedia.com - Original Message - From: Bruce Sorge [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, January 15, 2002 3:54 AM Subject: mass database update I am doing a mass update of a table. The query looks like this: cfloop index=thisItem from=1 to=#listlen(ID)# cfquery name=query_InsertItems datasource=firehook1 Update Inv1 Set Count = #Count# Where ID = #ListGetAt(ID, thisItem)# /cfquery /cfloop What I am getting is this: Update Inv1 Set Count = 9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9 Where ID = 2077 I know that I have to associate a quantity (count) with an ID, but I am not sure how to go about it. I tried nesting loops and creating arrays and such with no luck. Any assitance would be greatly appreciated. Thanks, _ Bruce Sorge __ 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=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: mass database update
use the value of thisItem. phillip -Original Message- From: Bruce Sorge [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 6:55 AM To: CF-Talk Subject: mass database update I am doing a mass update of a table. The query looks like this: cfloop index=thisItem from=1 to=#listlen(ID)# cfquery name=query_InsertItems datasource=firehook1 Update Inv1 Set Count = #Count# Where ID = #ListGetAt(ID, thisItem)# /cfquery /cfloop What I am getting is this: Update Inv1 Set Count = 9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9 Where ID = 2077 I know that I have to associate a quantity (count) with an ID, but I am not sure how to go about it. I tried nesting loops and creating arrays and such with no luck. Any assitance would be greatly appreciated. Thanks, _ Bruce Sorge __ 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
re: mass database update
use the value of thisItem. phillip __ 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: mass database update
At 05:54 AM 01/15/2002 -0600, you wrote: I am doing a mass update of a table. The query looks like this: cfloop index=thisItem from=1 to=#listlen(ID)# cfquery name=query_InsertItems datasource=firehook1 Update Inv1 Set Count = #Count# Where ID = #ListGetAt(ID, thisItem)# /cfquery /cfloop What I am getting is this: Update Inv1 Set Count = 9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9 Where ID = 2077 I don't see where count is defined, but if I understand the problem, but it sounds like the problem is with the Count variable. If you are trying to set every value to 9, you are complicating it greatly (but I suspect you are not trying to do that) -- Jeffry Houser | mailto:[EMAIL PROTECTED] AIM: Reboog711 | ICQ: 5246969 | Fax / Phone: 860-223-7946 -- DotComIt: Database Driven Web Data My Book: Instant ColdFusion 5 | http://www.instantcoldfusion.com My New Book: ColdFusion: A Beginner's Guide February 2002 -- Far Cry Fly, Alternative Folk Rock http://www.farcryfly.com | http://www.mp3.com/FarCryFly __ 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: mass database update
Count is a quantity amount for an item, and ID is the ID primary key of the item being updated. This is fed from a form that allows the user to update inventory quantities for any number of items. Rather than go in and update one item at a time, I want them to have the ability to do all the items at once. -- Original Message -- From: Bryan Stevenson [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Tue, 15 Jan 2002 10:01:15 -0800 # 5 needs more input... ;-) What is the relationship between count and ID and where does count ge t set? Bryan Stevenson VP Director of E-Commerce Development Electric Edge Systems Group Inc. p. 250.920.8830 e. [EMAIL PROTECTED] - Macromedia Associate Partner www.macromedia.com - Original Message - From: Bruce Sorge [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, January 15, 2002 3:54 AM Subject: mass database update I am doing a mass update of a table. The query looks like this: cfloop index=thisItem from=1 to=#listlen(ID)# cfquery name=query_InsertItems datasource=firehook1 Update Inv1 Set Count = #Count# Where ID = #ListGetAt(ID, thisItem)# /cfquery /cfloop What I am getting is this: Update Inv1 Set Count = 9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9 Where ID = 2077 I know that I have to associate a quantity (count) with an ID, but I am not sure how to go about it. I tried nesting loops and creating arrays and such with no luck. Any assitance would be greatly appreciated. Thanks, _ Bruce Sorge __ 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=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: mass database update
Bruce, If all your text fields on your form are named count then when you put data in them and submit the form all the fields with the same name are concatenated together to form a comma seperated list of the values submitted. What I'd suggest is naming the fields count#ID# or something that will make then unique and identifiable on the action page. This will separate out their values individually for processing. Another option would be to use your listGetAt() for your count variable as well, because it is a list too. So you have two options I guess. HTH, Tyler M. Fitch Certified Advanced ColdFusion 5 Developer ISITE Design, Inc. -Original Message- From: Bruce Sorge [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 1:57 PM To: CF-Talk Subject: Re: mass database update Count is a quantity amount for an item, and ID is the ID primary key of the item being updated. This is fed from a form that allows the user to update inventory quantities for any number of items. Rather than go in and update one item at a time, I want them to have the ability to do all the items at once. -- Original Message -- From: Bryan Stevenson [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Tue, 15 Jan 2002 10:01:15 -0800 # 5 needs more input... ;-) What is the relationship between count and ID and where does count ge t set? Bryan Stevenson VP Director of E-Commerce Development Electric Edge Systems Group Inc. p. 250.920.8830 e. [EMAIL PROTECTED] - Macromedia Associate Partner www.macromedia.com - Original Message - From: Bruce Sorge [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, January 15, 2002 3:54 AM Subject: mass database update I am doing a mass update of a table. The query looks like this: cfloop index=thisItem from=1 to=#listlen(ID)# cfquery name=query_InsertItems datasource=firehook1 Update Inv1 Set Count = #Count# Where ID = #ListGetAt(ID, thisItem)# /cfquery /cfloop What I am getting is this: Update Inv1 Set Count = 9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9 Where ID = 2077 I know that I have to associate a quantity (count) with an ID, but I am not sure how to go about it. I tried nesting loops and creating arrays and such with no luck. Any assitance would be greatly appreciated. Thanks, _ Bruce Sorge __ Why Share? Dedicated Win 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=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: mass database update
here is what I would do name all of the text boxes that are used to specify amounts similar to each other. with the primarykey part of the name TEXTBOX_#primaryKey# then loop the form colection cfloop list=#form.FIELNAMES# index=x cfif x CONTAINS TEXTBOX_ cfquery name=someQuery datasource=someDSN UPDATE TABLE SET column = evaluate(x) WHERE primarykey = #listLast(x, _)# /cfquery /cfif /cfloop let me know if you need any more clarification -chris.alvarado [application developer] 4|Guys Interactive, Inc. http://www.4guys.com - Original Message - From: Bruce Sorge [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, January 15, 2002 3:57 PM Subject: Re: mass database update Count is a quantity amount for an item, and ID is the ID primary key of the item being updated. This is fed from a form that allows the user to update inventory quantities for any number of items. Rather than go in and update one item at a time, I want them to have the ability to do all the items at once. -- Original Message -- From: Bryan Stevenson [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Tue, 15 Jan 2002 10:01:15 -0800 # 5 needs more input... ;-) What is the relationship between count and ID and where does count ge t set? Bryan Stevenson VP Director of E-Commerce Development Electric Edge Systems Group Inc. p. 250.920.8830 e. [EMAIL PROTECTED] - Macromedia Associate Partner www.macromedia.com - Original Message - From: Bruce Sorge [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, January 15, 2002 3:54 AM Subject: mass database update I am doing a mass update of a table. The query looks like this: cfloop index=thisItem from=1 to=#listlen(ID)# cfquery name=query_InsertItems datasource=firehook1 Update Inv1 Set Count = #Count# Where ID = #ListGetAt(ID, thisItem)# /cfquery /cfloop What I am getting is this: Update Inv1 Set Count = 9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9 Where ID = 2077 I know that I have to associate a quantity (count) with an ID, but I am not sure how to go about it. I tried nesting loops and creating arrays and such with no luck. Any assitance would be greatly appreciated. Thanks, _ Bruce Sorge __ Why Share? Dedicated Win 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=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: mass database update
well then, you should name the count field on the form something like count_appendProductID. then when you do the insert you could do something like: Update Inv1 Set Count = #evaluate(form.Count_ ID)# Where ID = #ListGetAt(ID, thisItem)# hope that helps :) phillip -Original Message- From: Bruce Sorge [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 4:57 PM To: CF-Talk Subject: Re: mass database update Count is a quantity amount for an item, and ID is the ID primary key of the item being updated. This is fed from a form that allows the user to update inventory quantities for any number of items. Rather than go in and update one item at a time, I want them to have the ability to do all the items at once. -- Original Message -- From: Bryan Stevenson [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Tue, 15 Jan 2002 10:01:15 -0800 # 5 needs more input... ;-) What is the relationship between count and ID and where does count ge t set? Bryan Stevenson VP Director of E-Commerce Development Electric Edge Systems Group Inc. p. 250.920.8830 e. [EMAIL PROTECTED] - Macromedia Associate Partner www.macromedia.com - Original Message - From: Bruce Sorge [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, January 15, 2002 3:54 AM Subject: mass database update I am doing a mass update of a table. The query looks like this: cfloop index=thisItem from=1 to=#listlen(ID)# cfquery name=query_InsertItems datasource=firehook1 Update Inv1 Set Count = #Count# Where ID = #ListGetAt(ID, thisItem)# /cfquery /cfloop What I am getting is this: Update Inv1 Set Count = 9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9 Where ID = 2077 I know that I have to associate a quantity (count) with an ID, but I am not sure how to go about it. I tried nesting loops and creating arrays and such with no luck. Any assitance would be greatly appreciated. Thanks, _ Bruce Sorge __ 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: mass database update
You know, I think that I need a little electro shock thereapy for each time I do this. I made this same mistake a couple of weeks ago. Man, these 15 h our days are getting to me (I know, I am a slacker). -- Original Message -- From: Tyler M. Fitch [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Tue, 15 Jan 2002 13:59:38 -0800 Bruce, If all your text fields on your form are named count then when you put data in them and submit the form all the fields with the same name are concatenated together to form a comma seperated list of the values submitted. What I'd suggest is naming the fields count#ID# or something that will make then unique and identifiable on the action page. This will separate out their values individually for processing. Another option would be to use your listGetAt() for your count variable as well, because it is a list too. So you have two options I guess. HTH, Tyler M. Fitch Certified Advanced ColdFusion 5 Developer ISITE Design, Inc. -Original Message- From: Bruce Sorge [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 1:57 PM To: CF-Talk Subject: Re: mass database update Count is a quantity amount for an item, and ID is the ID primary key of the item being updated. This is fed from a form that allows the user to update inventory quantities for any number of items. Rather than go in and update one item at a time, I want them to have the ability to do all the items at once. -- Original Message -- From: Bryan Stevenson [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Tue, 15 Jan 2002 10:01:15 -0800 # 5 needs more input... ;-) What is the relationship between count and ID and where does count ge t set? Bryan Stevenson VP Director of E-Commerce Development Electric Edge Systems Group Inc. p. 250.920.8830 e. [EMAIL PROTECTED] - Macromedia Associate Partner www.macromedia.com - Original Message - From: Bruce Sorge [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, January 15, 2002 3:54 AM Subject: mass database update I am doing a mass update of a table. The query looks like this: cfloop index=thisItem from=1 to=#listlen(ID)# cfquery name=query_InsertItems datasource=firehook1 Update Inv1 Set Count = #Count# Where ID = #ListGetAt(ID, thisItem)# /cfquery /cfloop What I am getting is this: Update Inv1 Set Count = 9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9 Where ID = 2077 I know that I have to associate a quantity (count) with an ID, but I am not sure how to go about it. I tried nesting loops and creating arrays and such with no luck. Any assitance would be greatly appreciated. Thanks, _ Bruce Sorge __ 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=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
