CODEWITHSUNDEEP
sql-serverwpfvb.nett-sql
rgorr
rgorr

Reputation: 380

Preserve row order in bulk insert

I'm trying to bulk insert a table from a text file and preserve the row order. I'm doing this for two reasons:

To do the bulk insert I am first inserting it into a temp table, and then inserting the cleaned up values into the main table. But the temp table seems to be in a random order.

The input file is not in any particular order, so I can't solve this with an order-by query.

Any suggestions on what I can do here?

Upvotes: 3

Views: 5699

Answers (4)

EricI
EricI

Reputation: 3816

Keying off of Christophej's suggestion with a few other additions:

CREATE TABLE dbo.twrkImportFileLine ( ID INT IDENTITY(1,1) PRIMARY KEY, LineData NVARCHAR(MAX) ); 

GO

CREATE OR ALTER VIEW dbo.vw_twrkImportFileLine AS
SELECT LineData
FROM twrkImportFileLine;

GO

--INSERT INTO vw_twrkImportFileLine (LineData) VALUES ('Test inserting text into the table through the view'); 
--SELECT * FROM vw_twrkImportFileLine
--DELETE FROM vw_twrkImportFileLine

BULK INSERT dbo.vw_twrkImportFileLine FROM '\\SERVERNAME\Import Folder\FileName.txt' WITH (TABLOCK) ;

SELECT LineData
FROM dbo.twrkImportFileLine 
ORDER BY ID;

The order was maintained in my 815 line sample file that I tested. It was previously out of order without this method in large sections most likely due to parallelism.

Upvotes: 0

christophej
christophej

Reputation: 35

Create a table with identity column, then bulk insert into a view on top of that table, as follows:

CREATE TABLE tb_TextFile (ID int identity(1,1), someColumn varchar(16))
GO

CREATE VIEW vw_TextFile
AS
SELECT someColumn
FROM tb_TextFile
GO

BULK INSERT vw_TextFile
FROM ...

Upvotes: 1

pprajapati
pprajapati

Reputation: 25

 Using _con As New SqlConnection(ConnectionString)
  _con.Open()
  If Not (String.IsNullOrEmpty(Pkey)) Then
    deleteTbl = "Delete from " & destTableName & " WHERE " & Pkey & " = " & PkeyId
    Using cmdTable As New SqlCommand(deleteTbl, _con)
      cmdTable.ExecuteNonQuery()
    End Using
  End If


  Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(_con)
    bulkCopy.BulkCopyTimeout = 0
    bulkCopy.BatchSize = 50000
    bulkCopy.DestinationTableName = destTableName 'it is a tablename
    bulkCopy.WriteToServer(sourceTable)  'sourceTable is a datatable
  End Using

End Using

Upvotes: 0

S3S
S3S

Reputation: 25112

For the table or temp table you are inserting to, just have a column with the IDENTITY PROPERTY

create table #temp (ID int identity(1,1), otherColumn varchar(16), ...)
--do bulk insert

But, what may be more ideal for you is to create a unique row number in the source file.

Upvotes: 1

Related Questions