Path: csiph.com!eternal-september.org!feeder.eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail
From: Erland Sommarskog <esquel@sommarskog.se>
Newsgroups: microsoft.public.sqlserver.programming
Subject: Re: Bulk Insert Erratic insert order. Any Help Greatly Appreciated.
Date: Sat, 29 Apr 2017 10:25:17 +0200
Organization: Erland Sommarskog
Lines: 20
Message-ID: <XnsA7666A03B9350Yazorman@127.0.0.1>
References: <bca21819-f64b-4e56-8958-55a765d92575@2g2000yqk.googlegroups.com> <f44a9b6e-bee0-4ca1-9d03-c66ff66d3c2f@googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
Injection-Info: mx02.eternal-september.org; posting-host="cda40ea684a86d7b463c62c9a74ffa46"; logging-data="4562"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX1/9sRLsquGAzhaoTSmKHIlp"
User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock: sha1:RE//KybTODi/e10HVV3Vm6k0Q2U=
Xref: csiph.com microsoft.public.sqlserver.programming: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.