Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!news-out.readnews.com!transit3.readnews.com!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail From: "Fred." Newsgroups: comp.databases.ms-sqlserver Subject: Re: Bulk Insert Erratic insert order. Any Help Greatly Appreciated. Date: Mon, 16 Apr 2012 10:50:37 -0700 (PDT) Organization: http://groups.google.com Lines: 42 Message-ID: <8966248.1022.1334598637220.JavaMail.geo-discussion-forums@ynlp2> References: NNTP-Posting-Host: 198.234.82.254 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 X-Trace: posting.google.com 1334598981 5058 127.0.0.1 (16 Apr 2012 17:56:21 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Mon, 16 Apr 2012 17:56:21 +0000 (UTC) In-Reply-To: Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=198.234.82.254; posting-account=H0CbGQoAAACbl_tS1RfYwvMt_jmh18zO User-Agent: G2/1.0 Xref: csiph.com comp.databases.ms-sqlserver:982 On Monday, April 16, 2012 11:10:45 AM UTC-4, eighthman11 wrote: > Good Morning. I'm doing a bulk insert in a stored procedure. The > source file is a tab delimited file which was created from an excel > spreadsheet. I'm using SQL Server 2008. Below is the code I am using > for the bulk insert. > > BULK INSERT #MyBulkData > FROM '\\Nccs-db03\UploadFolder\textfile.txt' > WITH(FIELDTERMINATOR = '\t') > > OK here's the problem. The excel spreadsheet which is the source file > that I save as a tab delimited text file has a column for "day > worked". The "day worked" is not on every row in the spreadsheet. > The day worked only appears when the "day worked" changes. So after I > do the Bulk import I loop through the records and fill in the blank > "day worked" field based on the previous "day worked" record. > > The problem is when doing the bulk insert I have to have the records > insert in the exact same order as the source file or I can't loop > through the records and update the proper "day worked". The bulk > insert; inserts the record in the exact same order as the text file > about 90 percent of the time, but occasionally it just goes crazy. > > I hope I am making sense with this and any help would be appreciated. > > Thanks > > Ray Since the insertion order is unspecified, you are taking a risk enven if you think you have worked out a way to control it. The next update may blow you out of the water. Assuming that you don't have multiple simultaneous processes the surest thing might be to add a record number field to the spreadsheet. Place the next available record number in the first row of an added column, the formula for that cell plus 1 in the second row, and propagate the second cell down the reamainder of the column. That puts you, not SQL server in control of the sequence. Fred.