Order By in Bulk Insert

Hi,

I need to read a text file through bulk insert command and insert into a temp table.Now i need to persists the sequence of the row as they are in text file.

Sample.txt
Name|Age|Gender|Address
John |12|M|New York
Adam|44|M|New York

In the temp data when data is saved the order of the records & header is not the same every time you run.
How can i achieve the insertion order the same as of file.

Thanks!
Tech NoviceAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill BachPresident and Btrieve GuruCommented:
SQL data, by its very definition, is a SET -- and sets are NOT ordered.  It is only the application of the ORDER BY statement that allows you to retrieve data in order.

Add a sequence number field to your temp table using a data type of Identity, like this: "Sequence_Number INT IDENTITY NOT NULL".  This will create another field in which the values will be auto-generated and auto-incremented as you insert the records into the table.

Then, to get the data out, use ORDER BY Sequence_Number, and your data will be in the order you desire.
1
Vitor MontalvãoMSSQL Senior EngineerCommented:
Unless you have a field or a set of fields that can be used to define a cluster index, you can't do what you required.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
BULK INSERT - Implies that this needs to be done fast.
ORDER BY - A very slow operator as SQL has to take the entire set in memory and sort.

If the above comments don't get you home, please provide us more details as to why this is important.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Tech NoviceAuthor Commented:
@:Bill Bach
Add a sequence number field to your temp table using a data type of Identity, like this: "Sequence_Number INT IDENTITY NOT NULL".  This will create another field in which the values will be auto-generated and auto-incremented as you insert the records into the table.


Since the header are inserted at 10th row then id would be 10,other time if it is inserted at 105th row id would be 105..

I would anyhow will not get the desired result as  i want header should always be at fixed no,no matter what it could be either 10 or 105,but it should remain the same every time....
0
Bill BachPresident and Btrieve GuruCommented:
Why on earth would you insert the HEADER row into the table?

If Bulk Load is actually inserting data out of order, then you cannot use bulk load.  Instead, just insert each row, one at a time.  It will be slower, but you will have the guaranteed order secured through the Identity column.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
you will have the guaranteed order secured through the Identity column.
That's not true. Only way to ensure that a table is physically order by a column value is to create a clustered index with that column.
0
Tech NoviceAuthor Commented:
@Vitor Montalvão

Only way to ensure that a table is physically order by a column value is to create a clustered index with that column.

If i know that the two columns will have numeric data whereas in header it will contain the alphabets then it i would get the header row as the first row.
0
Mark WillsTopic AdvisorCommented:
The major attraction of BULK is it is minimally logged, so long as a few conditions are met : https://docs.microsoft.com/en-us/sql/relational-databases/import-export/prerequisites-for-minimal-logging-in-bulk-import

In that regard, I always import into a staging table first.

Especially when sequences / headers and a bit of manipulation is required.

Definitely need an Identity column to control sequence.

Fortunately, by creating an empty staging table, we could have a clustered index on an Identity column. However you need to be aware of any clustered index because that will override an import 'order by'

Not quite sure of the relationship of header records as part of the import requirements and keeping them in sequence. Maybe some sample data that best represents what data you need to import with a bit more guidance on sequencing given that sample data.

But, create an empty staging table with an Identity and import into that - no 'ORDER BY' and maybe no clustered indexes for the staging table until we know more of your requirements.

There are quite a few conditions than can affect minimal logging.

Sure, when it comes down to putting data away, logging starts, but will most likely happen anyway if going to a non-empty table....

So, can we get some sample data with explanation of 'sequence' and 'headers'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tech NoviceAuthor Commented:
Thanks ALL
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.