February 14, 2012 at 4:52 am
I Have a below query
create table #temp1(
ID int identity(1,1),
Two int,
Name varchar(10) )
INSERT INTO #temp1
SELECT 2,'BCA'
UNION
SELECT 4,'ABCD'
UNION
SELECT 5,'Bdc'
UNION
SELECT 1,'XXABCD'
select Name,ID,Two into #temp2 from #temp1
order by Name
select * from #temp1
select * from #temp2
The Output is
Temp#1
ID Two Name
----------- ----------- ----------
1 1 XXABCD
2 2 BCA
3 4 ABCD
4 5 Bdc
Temp#2
Name ID Two
---------- ----------- -----------
ABCD 3 4
BCA 2 2
Bdc 4 5
XXABCD 1 1
(4 row(s) affected)
But i want the Temp#2 result set as below can any one help me.
Name ID Two
---------- ----------- -----------
ABCD 1 4
BCA 2 2
Bdc 3 5
XXABCD 4 1
(4 row(s) affected)
February 14, 2012 at 4:55 am
SELECT * FROM #temp2
ORDER BY ID
John
February 14, 2012 at 4:57 am
while inserting the #temp2 the name and ID (identity ) columns should be in order
February 14, 2012 at 5:00 am
BEGIN TRAN
--Your sample data
CREATE TABLE [Temp#1] (ID INT, Two INT, Name VARCHAR(6));
INSERT INTO [Temp#1]
SELECT 1, 1, 'XXABCD'
UNION ALL SELECT 2, 2, 'BCA'
UNION ALL SELECT 3, 4, 'ABCD'
UNION ALL SELECT 4, 5, 'Bdc';
--Insert statement
SELECT Name, Two, ROW_NUMBER() OVER (ORDER BY Name) AS ID
INTO [Temp#2]
FROM [Temp#1];
--View results
SELECT * FROM [Temp#1];
SELECT * FROM [Temp#2]
ORDER BY ID;
ROLLBACK
February 14, 2012 at 5:01 am
No they shouldn't. A table is an unordered set. The database engine will retrieve rows from a table in the way and in the order it judges most expedient. The only way to guarantee what order they come out in is to use an ORDER BY clause.
John
February 14, 2012 at 5:01 am
ID is an identity column, you shouldn't try to force it into a specific order.
Furthermore, it is useless to specify an ORDER BY on an INSERT.
ORDER BY Clause (Transact-SQL)
When used together with a SELECT...INTO statement to insert rows from another source, the ORDER BY clause does not guarantee the rows are inserted in the specified order.
If you want the data to be returned in a specific order, then use an ORDER BY on the SELECT statement.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 14, 2012 at 5:01 am
select Name,Two into #temp2 from #temp1
order by Name
alter table #temp2
add <NewColumn ID> int identity(1,1)
Add ID column later.
Vasu
February 14, 2012 at 5:10 am
Thank You For Your Help Again Thank You So Much
Website solution | Web development Company | Web application
February 14, 2012 at 5:11 am
John Mitchell-245523 (2/14/2012)
SELECT * FROM #temp2ORDER BY ID
John
John Mitchell-245523 (2/14/2012)
No they shouldn't. A table is an unordered set. The database engine will retrieve rows from a table in the way and in the order it judges most expedient. The only way to guarantee what order they come out in is to use an ORDER BY clause.John
Koen Verbeeck (2/14/2012)
ID is an identity column, you shouldn't try to force it into a specific order.Furthermore, it is useless to specify an ORDER BY on an INSERT.
ORDER BY Clause (Transact-SQL)
When used together with a SELECT...INTO statement to insert rows from another source, the ORDER BY clause does not guarantee the rows are inserted in the specified order.
If you want the data to be returned in a specific order, then use an ORDER BY on the SELECT statement.
I think you guys missed the OPs result-set.
ID Two Name
----------- ----------- ------
1 1 XXABCD
2 2 BCA
3 4 ABCD
4 5 Bdc
Name Two ID
------ ----------- --------------------
ABCD 4 1
BCA 2 2
Bdc 5 3
XXABCD 1 4
Look at his "ID" column. You'll see that he essentially wanted to order that by the "Name", rather than using what was already stored in [Temp#1].
Obviously I agree with you both that SQL Server has no physical ordering, and therefore ordering the set in the INSERT is incorrect, but this works as requested: -
--Insert statement
SELECT Name, Two, ROW_NUMBER() OVER (ORDER BY Name) AS ID
INTO [Temp#2]
FROM [Temp#1];
asiaindian (2/14/2012)
select Name,Two into #temp2 from #temp1order by Name
alter table #temp2
add <NewColumn ID> int identity(1,1)
Add ID column later.
Vasu
That isn't guaranteed to work. As Koen pointed out, an INSERT with an ORDER BY is not guaranteed to actually ORDER the set.
February 14, 2012 at 5:21 am
Cadavre (2/14/2012)
Look at his "ID" column. You'll see that he essentially wanted to order that by the "Name", rather than using what was already stored in [Temp#1].
Well, the OP said that he wanted to do it while inserting:
while inserting the #temp2 the name and ID (identity ) columns should be in order
As many pointed out, that is not possible. It is possible in the SELECT statement, as you have clearly demonstrated:
Cadavre (2/14/2012)
..., but this works as requested: -
--Insert statementSELECT Name, Two, ROW_NUMBER() OVER (ORDER BY Name) AS ID
INTO [Temp#2]
FROM [Temp#1];
So your solution comes closest to the OP's expectations 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 14, 2012 at 5:37 am
Koen Verbeeck (2/14/2012)
Cadavre (2/14/2012)
Look at his "ID" column. You'll see that he essentially wanted to order that by the "Name", rather than using what was already stored in [Temp#1].Well, the OP said that he wanted to do it while inserting:
My hope is that comment from the OP was a language barrier thing, or a simple misunderstanding of how SQL Server works. As I said, I agree with both of you that INSERT with an ORDER BY is useless.
However, the result-set requested is indicative of simply requiring the "ID" to be ordered by "Name" in [Temp#2]
February 14, 2012 at 8:53 am
ACtually - using an ORDER BY during the insert would guarantee the ID's be generated in the correct order. Conor Cunningham confirmed that to be true a long while back.
Note that that is the ONLY guarantee. Has nothing to do with which order they were physicially being inserted in.
So the functionality the OP is looking for IS in fact accomplished by adding an order by into the INSERT.
create table #temp1(
ID int identity(1,1),
Two int,
Name varchar(10) )
INSERT INTO #temp1 (two,name)
SELECT 2 as two, 'BCA' as [name]
UNION
SELECT 4,'ABCD'
UNION
SELECT 5,'Bdc'
UNION
SELECT 1,'XXABCD'
order by [name]
select * from #temp1 order by id
(Yes - you still need the ORDER by in the results if you want the rows to show up in the correct order, but the ID's were assigned in the expected order this time).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 14, 2012 at 10:19 am
Koen Verbeeck (2/14/2012)
ID is an identity column, you shouldn't try to force it into a specific order.Furthermore, it is useless to specify an ORDER BY on an INSERT.
ORDER BY Clause (Transact-SQL)
When used together with a SELECT...INTO statement to insert rows from another source, the ORDER BY clause does not guarantee the rows are inserted in the specified order.
If you want the data to be returned in a specific order, then use an ORDER BY on the SELECT statement.
I've found that the only time there's a problem with that is... {edit: In a non-partitioned table}
1. If parallelism occurs and that's easily taken care of with OPTION(MAXDO 1)
2. If the ORDER BY operand contains something indeterminate such as GETDATE().
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2012 at 12:07 pm
When used together with a SELECT...INTO statement to insert rows from another source, ...
I take that to mean what it explicitly says -- if you do SELECT ... INTO <new_table>, the order is not guaranteed.
For a standard INSERT INTO, I thought ORDER BY is in fact enforced.
I hope so, because it's extremely useful, as you can sort data by the clus index column(s) for the receiving table.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 14, 2012 at 12:31 pm
ScottPletcher (2/14/2012)
When used together with a SELECT...INTO statement to insert rows from another source, ...
I take that to mean what it explicitly says -- if you do SELECT ... INTO <new_table>, the order is not guaranteed.
For a standard INSERT INTO, I thought ORDER BY is in fact enforced.
I hope so, because it's extremely useful, as you can sort data by the clus index column(s) for the receiving table.
Yes, but you can sort the data by any column any time. Creat a NC index. It just seems to be too much work when you still need to retrieve results in some order. If you don't then who cares? 🙂
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply