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

Groups > comp.databases.ms-sqlserver > #978 > unrolled thread

Bulk Insert Erratic insert order. Any Help Greatly Appreciated.

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

#978 — Bulk Insert Erratic insert order. Any Help Greatly Appreciated.

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

[toc] | [next] | [standalone]

#980

On Mon, 16 Apr 2012 08:10:45 -0700 (PDT), eighthman11
<eighthman11@yahoo.com> wrote:
  
>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.

Are you using an Int identity field and then subsequently sorting by
that column?  In Access if you have an autonumber field then the
records will be sorted in insert sequence if you sort by that field.

Now a problem can arise if multiple folks are doing the insert at the
same time.

Tony
-- 
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files 
  updated see http://www.autofeupdater.com/

[toc] | [prev] | [next] | [standalone]

#981

On Apr 16, 12:17 pm, Tony Toews <tto...@telusplanet.net> wrote:
> On Mon, 16 Apr 2012 08:10:45 -0700 (PDT), eighthman11
>
> <eighthma...@yahoo.com> wrote:
> >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.
>
> Are you using an Int identity field and then subsequently sorting by
> that column?  In Access if you have an autonumber field then the
> records will be sorted in insert sequence if you sort by that field.
>
> Now a problem can arise if multiple folks are doing the insert at the
> same time.
>
> Tony
> --
> Tony Toews, Microsoft Access MVP
> Tony's Main MS Access pages -http://www.granite.ab.ca/accsmstr.htm
> Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
> For a convenient utility to keep your users FEs and other files
>   updated seehttp://www.autofeupdater.com/

I'm inserting into a temp table on the SQL Server.  The bulk insert is
a stored procedure that is being called by a Crystal Report.

[toc] | [prev] | [next] | [standalone]

#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.

[toc] | [prev] | [next] | [standalone]

#983

On Mon, 16 Apr 2012 08:10:45 -0700 (PDT), eighthman11
<eighthman11@yahoo.com> wrote:

>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.
>
>Ray

Perhaps a dumb question ... but why not correct the data before
importing it?  I would just write a little program to insert the
missing fields while copying the original data.

As an aside, YMMV but personally I avoid the use of tabs as
delimiters.  Tabs are nice for visually examining a file, but a
printable character is better for programmatic manipulation.  If you
need to, you always can pipe the file through a filter to change the
field delimiters to tabs.

George

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web