Asked by:
SQL-Order by clause not working

Question
-
Hi Experts ,
I am not able to insert Data from One Table to another using Order By Clause ,Below is the workAround I tried .
CREATE TABLE TEMP1(ID INT ,DATES DATE)
INSERT INTO TEMP1
SELECT 1,'2011-03-07' UNION ALL
SELECT 2,'2010-03-06' UNION ALL
SELECT 3,'2013-03-01' UNION ALL
SELECT 4,'2009-03-21'SELECT * FROM TEMP1
/*BELOW CODE DOES NOT GIVE THE REQUIRED OUTPUT I.E I DO NOT GET THE OUTPUT OF TEMP2 TABLE ORDER BY DATES*/
SELECT * INTO TEMP2 FROM TEMP1 ORDER BY DATES
SELECT * FROM TEMP2/*SECOND APPROACH ALSO DOES NOT WORK FOR SAME SCENARIO*/
CREATE TABLE TEMP3 (ID INT,DATES DATE)
INSERT INTO TEMP3
SELECT ID,DATES FROM TEMP1 ORDER BY DATESSELECT * FROM TEMP3
/*HOW DO I INSERT DATA FROM ONE TABLE TO OTHER WITH ORDER BY ??*/
Wednesday, March 6, 2013 1:11 PM
All replies
-
Your tables have no "order" they are heaps. If you had a clustered index on dates in Temp3 then the data would be effectively ordered in the table by dates, but you would still need to Select * from temp3 Order by dates to gurantee that they came out in any particular order.
Chuck Pedretti | Magenic – North Region | magenic.com
- Proposed as answer by Lawrence A. Freeman Wednesday, March 6, 2013 1:56 PM
Wednesday, March 6, 2013 1:13 PM -
Check this post,
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/aeb1053d-0dc2-4dc3-96f5-8b213fed9669
http://blogs.msdn.com/b/sqltips/archive/2005/07/20/441053.aspx
Regards, Dineshkumar
Please "Mark as Answer" if my post answers your question and "Vote as Helpful" if it helps you- Edited by Dinesh Kumar Rajendran Wednesday, March 6, 2013 1:39 PM
Wednesday, March 6, 2013 1:38 PM -
It is not guaranteed that the data will be inserted in specific order. Just use ORDER BY clause in SELECT statement.
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
Wednesday, March 6, 2013 2:12 PMAnswerer -
Hi Uri
Thanks for the Reply.
I am already using Orderby Clause in my Select query i.e
CREATE TABLE TEMP3 (ID INT,DATES DATE)
INSERT INTO TEMP3
SELECT ID,DATES FROM TEMP1 ORDER BY DATESSELECT * FROM TEMP3
/*Copied from above thread*/
But still Data in TEMP3 is same as TEMP1..it is not sort by date in Required order .
Data in TEMP3 Table should look like
ID DATES
4 2009-03-21
2 2010-03-06
1 2011-03-07
3 2013-03-01
HOW DO I DO THAT ?- Edited by Rihan8585 Thursday, March 7, 2013 8:55 AM
Thursday, March 7, 2013 8:52 AM -
You won't able to sort the records when you insert, use a select query with order by after inserting into table
I think when we create keys and index on columns, it gets sorted. Am i right ?
"INSERT queries that use SELECT with ORDER BY to populate rows guarantees how identity values are computed but not the order in which the rows are inserted "
http://blogs.msdn.com/b/sqltips/archive/2005/07/20/441053.aspx
Regards, Dineshkumar
Please "Mark as Answer" if my post answers your question and "Vote as Helpful" if it helps you
- Edited by Dinesh Kumar Rajendran Thursday, March 7, 2013 10:31 AM
Thursday, March 7, 2013 10:27 AM