I'm having a problem with the way integers are stored internally and how they are returned with a SELECT statement. In the project I'm working on, I have to move data from an Excel file to a database so I'm using DTS and a group of stored procs to pull this off. What happens is:
DTS pull data from Excel into temp tables Stored proc pulls from the first temp table into a supertype table IDENTITY column assigns primary keys Stored proc then loads primary keys into a cursor and, starting with the LAST record, moves the PK into the appropriate subtype table and assigns the correct foreign key enteries. Next stored proc pulls data from the second temp table into the supertype tabel Repeat process until all data is stored
I'm starting with the last record so I know that it will be from the most recent transfer from a temp table. Unfortunately the order of records that the cursor returns is not the order that they were inserted into the database. So when I go to transfer the records with PK numbers 258 and 257 (the last 2 inserted into the supertype) I end up moving the records with PK's 204 and 203 (the last 2 in the cursor). Is there any way to sort the records as they go into or come out of the cursor so the smallest number is first and the largets number last? Also, there are probably a thousand ways to do this more efficiently so suggestions there are also welcome. Thanks
I think I figured out a solution that doesn't use cursors and won't be affected by internal storage issues. However, comments and ideas are still welcome.|||Having staging table(s) is a very good thing when it comes to data import/export. If you can, try to eliminate dts from the process to save some cpu cycles. You'd want to look up linked server/openquery/openrowset/opendatasource in book online/google for some details.
No comments:
Post a Comment