Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #982

Re: Bulk Insert Erratic insert order. Any Help Greatly Appreciated.

Show all headers | View raw

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.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Bulk Insert Erratic insert order. Any Help Greatly Appreciated. eighthman11 <eighthman11@yahoo.com> - 2012-04-16 08:10 -0700
  Re: Bulk Insert Erratic insert order. Any Help Greatly Appreciated. Tony Toews <ttoews@telusplanet.net> - 2012-04-16 11:17 -0600
    Re: Bulk Insert Erratic insert order. Any Help Greatly Appreciated. eighthman11 <eighthman11@yahoo.com> - 2012-04-16 10:49 -0700
  Re: Bulk Insert Erratic insert order. Any Help Greatly Appreciated. "Fred." <ghrno-google@yahoo.com> - 2012-04-16 10:50 -0700
  Re: Bulk Insert Erratic insert order. Any Help Greatly Appreciated. George Neuner <gneuner2@comcast.net> - 2012-04-16 23:56 -0400

csiph-web