April 7, 2016 at 3:42 pm
I have a flat file of data where the row order is important but no field in the file can be used to determine the proper order. I plan to BULK INSERT the file into a temp table that has an IDENTITY column. Can I be guaranteed that the IDENTITY column would represent the ordering of the data as it was in the data file? Below is a simplified representation of my elements.
temp table:
CREATE TABLE #OrderTest(
Field1 VARCHAR(500) NULL,
Field2 VARCHAR(500) NULL,
TempID INT IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
BULK INSERT statement:
BULK INSERT #OrderTest FROM 'c:\Data.txt' WITH( FORMATFILE='C:\OrderTest.fmt')
Format file:
11.0
2
1 SQLCHAR 0 500 "\t" 1 Field1 SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 500 "\r" 2 Field2 SQL_Latin1_General_CP1_CI_AS
And to be clear, I don't necessarily need the IDENTITY column (TempID) to be the same as the data file row number. I just need to make sure that the following would replicate the data file
SELECT Field1, Field2 FROM #OrderTest ORDER BY TempID
April 7, 2016 at 4:19 pm
Insert order is not guaranteed when using BULK INSERT. I would recommend using SSIS for this, which can read and process your file in the order in which it is written.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 7, 2016 at 5:20 pm
If the incoming data is sorted in clustering key order, you can add an:
ORDER ( { column [ ASC | DESC ] } [ ,... n ] )
clause to the BULK INSERT which I believe will guarantee the order.
If it's an identity (arrgh, most often not best for overall performance), you'll probably also need to specify:
KEEPIDENTITY
in the BULK INSERT.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 8, 2016 at 5:59 am
Thank you, Phil. Not what I was hoping for, but at least I know. SSIS is not something that I'm familiar with (and little time to learn it at the moment) so I will have to look for an alternative.
April 8, 2016 at 6:09 am
Scott,
Other than the order of the rows themselves, there is nothing about the data that I can rely upon to order it. If I understand how the ORDER option works for BULK INSERT, I think that it would mess it up if I specified ORDER. Also, I couldn't use the KEEPIDENTITY option since my data doesn't have an identity column; the identity column was in the temp table that was being inserted into.
The IDENTITY column in the temp table was only going to be used to aid in processing the data so I could import it into a more normalized structure.
Thanks for commenting! 🙂
April 8, 2016 at 6:14 am
JTSash (4/8/2016)
Thank you, Phil. Not what I was hoping for, but at least I know. SSIS is not something that I'm familiar with (and little time to learn it at the moment) so I will have to look for an alternative.
A possible short-term fix is to pre-process the file and add a row number column.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 8, 2016 at 6:26 am
Yes, I have already contacted the person that I get the data from to see if they are able to add a column with the row number. 🙂
April 8, 2016 at 7:27 am
If for some reason the data provider cannot add a sequence number to the data, you can use a Scrpt Component configured as a (synchronous) transform in your data flow to add a row sequence number to your data.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply