On 2/8/07, Scott Weikert <[EMAIL PROTECTED]> wrote: > > Anyone out there who's made use of the "bulk insert" functionality in MS > SQL Server - reasonably extensive use - please drop me an email > off-list. I've got some questions, specifically to do with mixed > delimiters, using quotes to get around mixed delimiters, and using the > "text" datatype with Bulk Insert.
Scott, We use bulk insert every day to duplicate about 6 gig of data, millions and millions of rows in about 50 tables. The data is FTPed from our mainframe every night to our SQL server, and I run a series of bulk insert tasks to insert the data.. every day. All of it :) I've never had to use mixed delimiters though. nor have I used the text data type either. What you need to do is use a field delimiter that is not *ANYWHERE* in your delimited file. Like a chr(1) or some totally whack character. Then, use another totally whack character as your row delimiter. This SHOULD allow you to have carriage returns in the data of those "text" fields. However, not sure how well that would work on extremely large text fields. IF at all. Like I said, never done that. But you definately can't use the standard delims. We use pipe delimited files, which SOMETIMES cause issues but rarely. There has been a time or two when some schmuck used a pipe in an address. 123 main street | apt 1 .. but we have no text fields, just plain old varchars, and there are never carriage returns in our data, so we use the default row delimiter. Rick -- > I'm not certified, but I have been told that I'm certifiable... > Visit http://www.opensourcecf.com today! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269268 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
