I use bcp all the time on large text file imports and have seen no problem. As far as designs, you use bcp to import the feeds/data in a 'staging table', and typically, this table may have a primary key and no other indexes.
Of course, this is done within a stored procedure with error handling and logging around it. Once the import has gone through successfully, you then have another routine that taps into your staging table and pull out the data, transform it, perform aggregations, and incorporate it into your transactional database model or data warehouse model. -----Original Message----- From: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Sent: Thursday, July 27, 2006 12:43 AM To: CF-Talk Subject: Re: reading large text file Well OK, true but there are lots of other cavests to think about (explained in BOL). Basically if and when I have used BCP it has been in fast mode as the table was a one off creation with indexes or other associated oddities - usually just testing. BCP is only really effective in the case of a table which has no other additions to it (no indexes, triggers etc). You do not want to perform a slow BCP operation on say 50 million records without a post backup of both the DB/Transaction log and even then it may not always do what you want it to as any additional things could be missed off/not fired such as trigger operations. BCP should be avoided if at all possible in a production environment (on a well design DB and of course on SQL server :) "This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com -----Original Message----- From: Jim Wright <[EMAIL PROTECTED]> To: CF-Talk <[email protected]> Sent: Thu Jul 27 01:08:55 2006 Subject: Re: reading large text file > If you mean BCP then I would advise against it :-) it is not logged. BCP is logged...it can be minimally logged if certain criteria are met. -- Jim Wright Wright Business Solutions [EMAIL PROTECTED] 919-417-2257 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247884 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
