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

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

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

Show all headers | 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