SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


order of rows during bulk insert


order of rows during bulk insert

Author
Message
basava_v
basava_v
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 6
Hi,

When a bulk insert is done from a flat file or a xls file, how do u ensure the rows are added in the same order as they are in the xls file?

Basically, I want to load the data into a table which has an identity column which would need to store the rownumber of each record in the original flat file or xls file.


Thanks.
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34385 Visits: 16649
Is the data in the spreadsheet ordered according to any particular column(s)? If so, import the data and create a clustered index on that/those column(s). If you insist on having an identity column as well, then import into a staging table and then move into a table with an identity column.

John
basava_v
basava_v
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 6
We need to migrate data from a ms access db to sql server. As ms access preserves the records in the order in which they were actually inserted, and the application logic depends on it (the last() function in ms access) , we need to maintain the same order in the sql server db.

So, we thought of having an identity col which can be used for replicating the last() functionality in ms access.

The problem is, while migrating the existing data, the id column should be the actual position of that record in the original ms access db.
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34385 Visits: 16649
So is there no natural key in your Access table, something like create_date or order_number? If not, how about adding an extra column and populating that with an ascending number sequence? Then you can import to a staging table on the SQL Server and insert to your main table from there.

Better still, take this opportunity to start using the relational database as it was intended, and create a "proper" key rather than the somewhat arbitrary order of insertion or physical storage.

John
Adam Haines
Adam Haines
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6232 Visits: 3135
Hi,

When a bulk insert is done from a flat file or a xls file, how do u ensure the rows are added in the same order as they are in the xls file?

Basically, I want to load the data into a table which has an identity column which would need to store the rownumber of each record in the original flat file or xls file.


Thanks.


Create a sequential key in the spreadsheet and bulk insert with identity insert on.



We need to migrate data from a ms access db to sql server. As ms access preserves the records in the order in which they were actually inserted, and the application logic depends on it (the last() function in ms access) , we need to maintain the same order in the sql server db.

So, we thought of having an identity col which can be used for replicating the last() functionality in ms access.

The problem is, while migrating the existing data, the id column should be the actual position of that record in the original ms access db.


Why not use the database upgrade wizard to put it in SQL for you?



My blog: http://jahaines.blogspot.com
Herb Overstreet-363572
Herb Overstreet-363572
Mr or Mrs. 500
Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)

Group: General Forum Members
Points: 582 Visits: 46
I find the BCP utility with the the -E parameter to be the best tool when dealing with identity values within a data row. BCP is the "Rodney Dangerfield" of the data movement methods. I no longer use the IMPORT/EXPORT Wizzard.
TIM.OGRADY
TIM.OGRADY
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 50
Is there a way to ensure that a flat file is loaded into sql in the exact sequence of the source file?

I am having a similar concern with a flat file. I need to ensure that the data loaded is loaded in the same sequence found in the flat file source. I have set up the target table with an identity column which has sequenced the rows 1,2,3,..etc. It appears that the first few rows match the source, but I need to confirm the file was loaded from top to bottom. Any help would be greatly appreciated. Thanks.



Kind Regards,
Tim
CoolThe stone age did not end due to a lack of stones, nor will the information age end due to a lack of data.Cool
LutzM
LutzM
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23497 Visits: 13559
TIM.OGRADY (6/19/2010)
Is there a way to ensure that a flat file is loaded into sql in the exact sequence of the source file?

I am having a similar concern with a flat file. I need to ensure that the data loaded is loaded in the same sequence found in the flat file source. I have set up the target table with an identity column which has sequenced the rows 1,2,3,..etc. It appears that the first few rows match the source, but I need to confirm the file was loaded from top to bottom. Any help would be greatly appreciated. Thanks.


Tim,
it's usually better to start a new thread for a new issue. If you found a thread that's close to the problem you have, you could put a link into your thread. But that's just a side note - no need to start a new thread with this issue...

The answer follows the pattern of this thread: do you have any kind of natural key that can be used to identify the row sequence of your source file (e.g. names, a date, a quantity or something else in ascending/descending order)?
If so, you could use a self-join on your target table together with ROW_NUMBER() to ensure an ascending sequence.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
TIM.OGRADY
TIM.OGRADY
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 50
Thanks Lutz,

Unfortunately there is no true key to the data, and there are millions of rows (Manually adding them isn't going to work). The rows are ordered in a very specific relationship for example the data might look like this:

1xxxxx
2xxxxxxx
3xxxxxxx
3xxxxxxx
3xxxxxxx
1xxxx
2xxxxxx
2xxxxxx
3xxx

So 1 would be the header, 2 would be additional info, an 3 would be the details. however, the 3 are only related to the twos that are above it, and 2's are related only to the 1's above. I guess I am hoping that SSIS in sql2005 would always process and load the file from top to bottom. If this isn't the case, I'm hoping for a method to force it to do so.

Again, Thanks.

Tim



Kind Regards,
Tim
CoolThe stone age did not end due to a lack of stones, nor will the information age end due to a lack of data.Cool
LutzM
LutzM
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23497 Visits: 13559
I strongly recommend you number the rows prior to import.
I'm sure there are tools available on the web that will add line numbers. I, personally, use a DOS batch file I found on a German web site (comments and description ar all in German, so I don't think it would help that much...)



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search