How to keep row order in SqlBulkCopy?

Question

I export data programmatically from Excel to SQL Server 2005 using SqlBulkCopy. It works great, the only problem I am facing is that it does not save the sequence of lines that I have in the Excel file. I don’t have a column for the order, I just want the records to be inserted in the same order as in the Excel spreadsheet.

I can not modify the Excel file and work with what I have. Sorting by any of the existing columns will result in disruption.

Please, help.

PS It ended up inserting an identifier column into a spreadsheet, there seems to be no way to save an order during export / import

Answer 1

I don’t think the row order is given or guaranteed by SQL unless you use the ORDER BY clause.

From Bill Vaughn's post ( http://betav.com/blog/billva/2008/08/sql_server_indexing_tips_and_t.html ):

Using order: Even if a table has a clustered index (which stores data in physical order), SQL Server does not guarantee that rows will be returned in this (or any specific) if the ORDER BY clause is not used.

Another link with information:

http://sqlblogcasts.com/blogs/simons/archive/2007/08/21/What-is-the-position-of-a-row--.aspx

Answer 2

, Bulk Insert, , Microsoft. , script, . , ( ) Microsoft, , .

, , .

, . :

• . ( !)
• script. . T-Sql, CMD. , .

Powershell , Sql. , BULK. , , . 500K + . - FAST.

, XML. XML. , XML. XML , .

, , (Char (13) + Char (10))

:

• IMPORT SQL ( OPENROWSET), XML. XML-.

• XML , [ID].

  ---------------------------------
  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);
  ---------------------------------
  

  • XML Line Feed.

  • , . .

, sql, FileName , 1 0 ( ).

300 . 5 .

Answer 3

Excel CSV, INSERT , , . Groovy, , :

def file1 = new File('c:\\temp\\yourSpreadsheet.csv')
def file2 = new File('c:\\temp\\yourInsertScript.sql')

def reader = new FileReader(file1)
def writer = new FileWriter(file2)

reader.transformLine(writer) { line ->
    fields =  line.split(',')

    text = """INSERT INTO table1 (col1, col2, col3) VALUES ('${fields[0]}', '${fields[1]}', '${fields[2]}');"""

}

"yourInsertScript.sql" , , .

Answer 4

, . , , , .