Problem in Importing Excel data into MS SQL 2000

Hi all,

I want to import MS Excel data into MS SQL 2000 and I am programming this in VB 6.0. I am referring the article http://support.microsoft.com/kb/316934/EN-US/ for this. I can able to import Excel data successfully into SQL database table. But the problem is, the order of exported data in SQL table is not matching that of MS Excel data. All the rows were jumbled, which made it totally unusable.

Please guide me how to Import Excel data into MS SQL in its original order only. Any other method other than what I am following is present; please suggest me to keep the original order.

Regards,

Rajeev Vandakar

Bangalore

As long as the data is correct on each row, there is no way to 'guarantee' that SQL Server will store data in any particular order -UNLESS there is a CLUSTERED index on the table, and if so, the data will be stored in the index order.

Otherwise, with SQL Server, we do NOT concern ourselves with the order of data storage, we use ORDER BY in our queries to cause the resultset to be in the order desired.

I suspect you may have a PRIMARY KEY with a CLUSTERED Index, and that is why it 'appears' that your data is being jumbled.

Check in Books Online about how to use the ORDER BY clause in your queries to un-jumble your data.

|||

Thank you Arnie Rowland for reply.

I can use ORDER BY clause in MS SQL after importing data. But this will not solve my problem here. Because the way data is present in Excel. In Excel some 12 colums present. Rows were organised in sections and to indicate start of a section 3rd colummn (meant for person name) have section name and remaining columns were left blank. So after importing into SQL, if I use ORDER BY clause, these rows, meant for sectin name will come in the top! If Excel data moves to SQL in as-it-is, same order, my problem will be solved. How to do this?

Regards,

Rajeev Vandakar

|||

As I indicated before, SQL Server (in fact the SQL language specification), clearly states that there is no guarantee about the order that data is stored in a table.

If you MUST load the table with data in a particular order, I suggest that you might wish to add a column to the Excel file, fill that column with some indicator of row order, and add a clustered index (or primary key) to the SQL table using that column.

Then the data will be ordered as you desire.

Without some way to place the rows in a particular order, SQL Server does not care, does not enforce order, and does not produce reliably ordered data.

sql