Sorting and Grouping

[Deam]

How do I update a table to group or sort the data in it?
I have a table tblEmpDep it lists all the employees and their dependents. I want to update this table so that the data is now grouped by EmpSSN or Order by EmpSSN.

any ideas ?

 

[Genimuse]

Do you mean display the table ordered that way, or literally change the order of the data in the table?

If the former, it's as easy as adding an "ORDER BY" clause to the end of your SQL string, like

SELECT * FROM MyTable ORDER BY EmpSSN

If you mean actually changing the order of the data in the table, inside the database itself, not on your page, then there's no easy way to do it. And for that matter, no good reason to do it, since ORDER BY will return it the way you want it.

 

[Deam]

I meant changing the order of the data in the table, inside the database itself.

My situation is that I am exporting data out of a sql database. Its a user interface. Inthe code the last thing it does is create a sql server table then exports it using bcp. If not in sql then maybe i can sort it in bcp...

 

[DreXor]

might need to look into a re-index

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[Genimuse]

Re-indexing might do what you need, as the index may be what forces the order during a bcp.

Otherwise the only way I've been able to do it was to copy the table into a temporary table, delete the first table, recreate it, and copy the data back.

If there are a bunch of foreign keys then it's an even bigger hassle, a huge hassle, but it's doable. The trick with foreign keys is to effectively de-normalized the table while copying it into the temporary table, and then re-normalized it on the way back out.

 

[DreXor]

additional hassle needing mention, is that the db would have to be completely inactive to do the table shuffle. with use of in DB re-indexing tools can still cause some down time but insignificant compared to making a checklist and crossing fingers.

being that this is an SQL DB you can use some of the admin tools to handle it, also could write a group of SP's that would order/sort/group what you need as you need/want it, replicate a temp table, fill it, re-assign keys, the whole shabang. then thes e sp's could be stanked into a macro sp to run all tables, all keys, all re-indexes..much testing this route tho.

last option worth mention, is replicate the DB minus the data, then import everything as you want it (SAVE the scripts generated with the imports), check it over(especially with your web pages, little shifts can make big effects), delete the contents of the replicate.
re-run all the saved scripts to replicate earlier actions that were valid, at a point just before a set downtime/maintenance, and perform the switcheroo.
i personally would be more comfortable with this scenario. for one you still have your live DB, you make a replicate "test" db to play with as you need, get a chance to test all pages in a staging environment without compromising anything, and should the roof cave in on the ordeal, you still have your live environment (i tend not to trust automation i didn't create, murphy's law and all )

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!