Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

order of rows during bulk insert Expand / Collapse
Author
Message
Posted Wednesday, December 19, 2007 3:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 25, 2008 12:33 PM
Points: 3, 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.
Post #434689
Posted Wednesday, December 19, 2007 4:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:59 AM
Points: 5,227, Visits: 9,437
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
Post #434698
Posted Wednesday, December 19, 2007 4:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 25, 2008 12:33 PM
Points: 3, 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.
Post #434701
Posted Wednesday, December 19, 2007 4:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:59 AM
Points: 5,227, Visits: 9,437
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
Post #434707
Posted Thursday, December 20, 2007 11:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, July 12, 2014 6:43 PM
Points: 2,278, Visits: 3,051
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
Post #435337
Posted Wednesday, February 13, 2008 1:46 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 7, 2010 6:52 PM
Points: 516, 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.
Post #455372
Posted Saturday, June 19, 2010 6:25 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, July 25, 2010 9:57 PM
Points: 40, 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
The stone age did not end due to a lack of stones, nor will the information age end due to a lack of data.
Post #940007
Posted Sunday, June 20, 2010 2:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 7,019, Visits: 12,909
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
Post #940022
Posted Sunday, June 20, 2010 12:40 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, July 25, 2010 9:57 PM
Points: 40, 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
The stone age did not end due to a lack of stones, nor will the information age end due to a lack of data.
Post #940060
Posted Sunday, June 20, 2010 3:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 7,019, Visits: 12,909
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
Post #940072
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse