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

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

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

Show all headers | View raw

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

Back to microsoft.public.sqlserver.programming | Previous | Next — Previous 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