August 14, 2013 at 7:05 am
Sql server 2008 r2 service pack 2
When trying to import a single varchar column file using bcp utility (no format file)
to a temporary table (## ) the rows are inserted not int the order of the file.
Doing the same thing with a regular table the order is maintaind
1. Is there a way to ensure that the file will be bulked insert with its order?
2. Is it a known Issue ?
:w00t:
August 14, 2013 at 7:39 am
Why do you need to keep a sort order during the insert? The sort order is only required during a display (SELECT) of the results and can be accomplished (and only guaranteed) with a ORDER BY statement.
August 14, 2013 at 7:47 am
HanShi is exactly correct. My question is why you'd need to have inserted rows in a particular order. I can definitely see the case where you have time-sensitive data that you're importing and you want it to appear sequentially in the table, but the table storage order isn't defined like that.
If you do need to control the sequence in which your time-sensitive imported data is inserted into your table, then here's an approach you can use to accomplish this.
1. Import into a temporary table. Don't worry about the order.
2. Insert into your real table using a SELECT on your temporary table with an ORDER BY clause on your datetime fields.
3. Drop the temporary table.
August 14, 2013 at 7:55 am
Ed Wagner (8/14/2013)
1. Import into a temporary table. Don't worry about the order.2. Insert into your real table using a SELECT on your temporary table with an ORDER BY clause on your datetime fields.
3. Drop the temporary table.
Depending on the structure of the destination table, SQL may well completely ignore that order by. The only thing the order by is needed for is assigning an identity, as such if there's no identity there's no requirement to honour that order by and SQL can replace it with an order by to match the clustered index or leave it out entirely if the table is a heap.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 14, 2013 at 7:58 am
Good point, Gail, thank you. I'd not even considered the possibility of a heap.
August 14, 2013 at 8:04 am
good points Gail...
Thanks 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 14, 2013 at 8:06 am
Thanks Guys
The file being read contains Receipt Header with it corresponding lines just after the Receipt Raw header
The file includes several Receipts
So Maintianing the read order is very important
August 14, 2013 at 8:23 am
Gil_Adi (8/14/2013)
The file being read contains Receipt Header with it corresponding lines just after the Receipt Raw headerThe file includes several Receipts
So Maintianing the read order is very important
A table, by definition, has no order. Hence any talk of maintaining insert order is meaningless. The rows in the table have no order, no matter how they were inserted.
Order is something that a result set has when queried, as a result of an ORDER BY specified on that select.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 14, 2013 at 8:47 am
Gil_Adi (8/14/2013)
Thanks GuysThe file being read contains Receipt Header with it corresponding lines just after the Receipt Raw header
The file includes several Receipts
So Maintianing the read order is very important
This stuff gets much easier if you can touch up the text file before importing, even if its as simple as prefixing a line number in each line. This way you have an explicit value that specifies the order that stays with the data once you import it.
I've also considered adding an identity column to the table I'm importing to but this leaves me wondering if this is too dependant on bcp not messing up the order of inserts.
August 14, 2013 at 11:49 am
To meet your demands you first have to define a table with an identity column. Then you need to read the file line-by-line and add these as seperate records to the table. After the complete file is read, you can display the content of the table ordering by the identity column.
Because you will process the file line-by-line I guess you'll get the best performance by using a client application (programming language like C#).
August 14, 2013 at 9:57 pm
Gil_Adi (8/14/2013)
Sql server 2008 r2 service pack 2When trying to import a single varchar column file using bcp utility (no format file)
to a temporary table (## ) the rows are inserted not int the order of the file.
Doing the same thing with a regular table the order is maintaind
1. Is there a way to ensure that the file will be bulked insert with its order?
2. Is it a known Issue ?
:w00t:
1. What is the CREATE TABLE syntax for your Temp Table?
2. How many rows are in the file?
3. Can you post the first 10 rows of the file?
4. Why is it important to maintain the same order in the table as what's in the file? (For curiosity sake)
Cancel the 4th question. I found the answer in a previous post on this thread.
--Jeff Moden
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply