BULK INSERT - Identity values not in sequence

Question

BULK INSERT - Identity values not in sequence

ThomasH

SSCertifiable

Points: 7437
More actions
December 11, 2003 at 3:20 pm

#107117

I have a ~1MB ~15K line TXT file I'm importing using
BULK INSERT ViewName From 'FileName' with (CODEPAGE = 'raw')
or
BULK INSERT #TbName From 'FileName' with (CODEPAGE = 'raw')
where table is something like (DATA Varchar(500), RID INT IDENTITY)
TXT file is CR/LF delimited text data.
It imports IN sequence if I do the following:
1. Create a permanent Table in TempDB
2. Create a View in "current DB" like SELECT DATA FROM TempDB..
3. Execute the BULK INSERT ViewName
It imports with some records OUT OF SEQUENCE when I:
1. Create a #Table Temp table
2. Execute the BULK INSERT #Table
* OUT OF SEQUENCE means: Imported RID = 116 is actually the 120th line in .TXT file, and RID = 117 is the 116th line in .TXT file. There are other incorrect RIDs throughout the data. It seems to happen every time, however if the input .TXT file changes, the RIDs out of sync will change also. (RIDs are approximate)
It would be helpfull to me to be able to use the #TempTable instead of a "permanent" table. Any thoughts?

Once you understand the BITs, all the pieces come together

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Why is the sequence important anyway? It only becomes important when retrieving the data. Then you can always use ORDER BY.

Or am I missing something?

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

ThomasH SSCertifiable Points: 7437 More actions · Answer 2

The physical sequence is important because I have to parse thru the .TXT "data file" that contains a multiple of different sub sections & parent followed by child records.

Once you understand the BITs, all the pieces come together

gljjr SSChampion Points: 10080 More actions · Answer 3

If that is the case I would write a batch file to add a "LineNumber" field to your text file and import it as a field in your table. Then use the same line number on your child records. Otherwise there is no guarantee that you will have the 2 records linked together.

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.