Ordering In Sql Server

I have a situation where I am importing many rows of data from text files. The import process occurs using SqlBulkCopy and initially loads into a staging table. I perform some val

Solution 1:

I found that I am able to preserve source table order using BULK INSERT into a file, followed by adding an identity.

Given a tab-delimited table, C:\MyTable.txt, where I intentionally moved rows out of order:

FileName    FileType
wmsetup log
bar txt
wmsetup10   log
WMSysPr9    prx
WMSysPrx    prx
Wudf01000Inst   log
xpsp1hfm    log
_default    pif
0   log
002391_ tmp
005766_ tmp

I ran the following and preserved the text file order in SQL Server:

IF EXISTS(
    SELECT1FROM sys.tables t
    INNERJOIN sys.schemas s on s.schema_id=t.schema_id
    WHERE t.name='myTable'AND t.[type]='U'AND s.name='dbo'
)
DROPTABLE myTable
GO

CREATETABLE dbo.myTable(FileName VARCHAR(80), FileType VARCHAR(30))
GO

BULK INSERT myTable FROM'C:\MyTable.txt'WITH (
firstrow=2
, fieldterminator='\t'
, rowterminator='\n'
)
GO

ALTERTABLE myTable ADD ID INTIDENTITY(1,1)
GO

SELECT*FROM myTable
GO

Result:

FileName        FileType ID
--------------- -------- -----------
wmsetup         log1
bar             txt      2
wmsetup10       log3
WMSysPr9        prx      4
WMSysPrx        prx      5
Wudf01000Inst   log6
xpsp1hfm        log7
_default        pif      80log9002391_         tmp      10005766_         tmp      11