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 DATES

SELECT * FROM TEMP3

/*HOW DO I INSERT DATA FROM ONE TABLE TO OTHER WITH  ORDER BY ??*/

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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 DATES

SELECT * 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 ?

Answer 5

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