Groups > microsoft.public.sqlserver.programming > #31315

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

X-Received	by 10.237.38.37 with SMTP id z34mr6209118qtc.40.1493419151101; Fri, 28 Apr 2017 15:39:11 -0700 (PDT)
X-Received	by 10.157.4.34 with SMTP id 31mr10883otc.2.1493419151027; Fri, 28 Apr 2017 15:39:11 -0700 (PDT)
Path	csiph.com!weretis.net!feeder6.news.weretis.net!feeder.usenetexpress.com!feeder1.iad1.usenetexpress.com!border1.nntp.dca1.giganews.com!nntp.giganews.com!t52no430685qtb.0!news-out.google.com!v18ni2272ita.0!nntp.google.com!c26no676433itd.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups	microsoft.public.sqlserver.programming
Date	Fri, 28 Apr 2017 15:39:10 -0700 (PDT)
In-Reply-To	<bca21819-f64b-4e56-8958-55a765d92575@2g2000yqk.googlegroups.com>
Complaints-To	groups-abuse@google.com
Injection-Info	glegroupsg2000goo.googlegroups.com; posting-host=38.140.27.42; posting-account=43QVogoAAAA1w-3DApK-Zl9oZUaylPxc
NNTP-Posting-Host	38.140.27.42
References	<bca21819-f64b-4e56-8958-55a765d92575@2g2000yqk.googlegroups.com>
User-Agent	G2/1.0
MIME-Version	1.0
Message-ID	<f44a9b6e-bee0-4ca1-9d03-c66ff66d3c2f@googlegroups.com> (permalink)
Subject	Re: Bulk Insert Erratic insert order. Any Help Greatly Appreciated.
From	deanseawa@gmail.com
Injection-Date	Fri, 28 Apr 2017 22:39:11 +0000
Content-Type	text/plain; charset=UTF-8
Lines	19
Xref	csiph.com microsoft.public.sqlserver.programming:31315

Show key headers only | View raw

As long as your file is a text file, with each row terminated with a standard CRLF, this approach should work (tested Sql2008):

   ---------------------------------
   Declare @X xml;
   ---------------------------------
   SELECT @X=Cast('<X>'+Replace([BulkColumn],Char(13)+Char(10),'</X><X>')+'</X>' as XML)
   FROM OPENROWSET (BULK N'\\FileServer\ImportFolder\ImportFile_20170120.csv',SINGLE_CLOB) T
   ---------------------------------
   SELECT [Record].[X].query('.').value('.','varchar(max)') [Record]
      ,ROW_NUMBER() OVER (ORDER BY (SELECT 100)) [ID]
   Into #TEMP 
   FROM @X.nodes('X') [Record](X);
   ---------------------------------

1) Execute the BULK IMPORT SQL statement (using OPENROWSET), encapsulating each record with XML tags.
   - Capture the results into an XML variable.

2) Parse the variable by the XML tags into a temp table, adding an incrementing [ID] column.

Now you can go through the temp table in original row order and make your updates.

Back to microsoft.public.sqlserver.programming | Previous | Next — Next in thread | Find similar

Thread

Re: Bulk Insert Erratic insert order. Any Help Greatly Appreciated. deanseawa@gmail.com - 2017-04-28 15:39 -0700
  Re: Bulk Insert Erratic insert order. Any Help Greatly Appreciated. Erland Sommarskog <esquel@sommarskog.se> - 2017-04-29 10:25 +0200

csiph-web