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

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

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

Back to article view | Back to microsoft.public.sqlserver.programming

This discussion starts older than the indexed window; earlier articles aren't shown. The article labeled Started by below is the oldest one visible, not the original post.

Contents

  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

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

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.

[toc] | [next] | [standalone]

#31316

 (deanseawa@gmail.com) writes:
> 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);
>    ---------------------------------
> 

Note that this will fall flat if the file includes characters that are
special to XML.

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.sqlserver.programming

csiph-web