Inserting Data Into a Temp Table

Hi-

I am trying to insert data into a temp table using an Order By. The insert works but the Order By does not.

Can anyone help?
DECLARE @tmpTable1 TABLE 
( 
       ProductId VARCHAR(32)
        
) 
 
 
SELECT ProductId INTO tmpTable1 FROM RealTable1 WHERE UserId=@UserId ORDER BY RealTable1.MyId ASC;

Open in new window

doctor069Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zwseemmCommented:
The order is controlled by the table you are inserting the data into.
0
Steve HoggITCommented:
This will help. Include the MyId in your Temp Table.
DECLARE @tmpTable1 TABLE 
( 
	MyId INT,
	ProductId VARCHAR(32)
) 
 
Insert Into @tmpTable1 Values(1,'IYKY')
Insert Into @tmpTable1 Values(5,'YFI')
Insert Into @tmpTable1 Values(9,'STUFF')
Insert Into @tmpTable1 Values(17,'HOGG')
Insert Into @tmpTable1 Values(21,'ZILLA')
Insert Into @tmpTable1 Values(39,'CHICKS')
Insert Into @tmpTable1 Values(11,'HOGG')
Insert Into @tmpTable1 Values(2,'ZILLA')
Insert Into @tmpTable1 Values(14,'CHICKS')
--SELECT MyId, ProductId INTO @tmpTable1 FROM RealTable1 WHERE UserId=@UserId ORDER BY RealTable1.MyId ASC;
 
Select * From @tmpTable1
Order By MyId

Open in new window

results.bmp
0
doctor069Author Commented:
Unfortunately no...

The whole reason I am putting it into a temp table is because I am using the final Select Statement in a subquery so I can't use an Order By.

What i was hoping for is to order them properly into the temp table and the use a simple select.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

hongjunCommented:
You can consider inserting rows into the temp table in a sequence whereby ProductID is in ascending order.
You may wish to post more of your sql.
Why not Order By?
0
zwseemmCommented:
Put it in the table without the order by and then use the order by when you select it back out.
0
Anthony PerkinsCommented:
>>What i was hoping for is to order them properly into the temp table and the use a simple select.<<
That is not the way it works.  Unless you use and ORDER BY clause when selecting the data, there is no guaranteeing the order the rows will be returned.
0
doctor069Author Commented:
when I am selecting back out in can't put an order by but this is in a subquery and order by is not allowed...


DECLARE @tmpTable1 TABLE 
( 
       ProductId VARCHAR(32)
        
) 
--Get the data into a temp table I am hope to get it in an order!!!
SELECT ProductId INTO tmpTable1 FROM RealTable1 WHERE UserId=@UserId ORDER BY RealTable1.MyId ASC;
 
--I cant do the order by in this part because it is a subquery
Select * from TableProducts
where ParentNumber IN (select ProductId from tmpTable1)
 
 
 

Open in new window

0
hongjunCommented:
You should be concern with the final result of Select * From TableProducts isn't it?
Do a order by on your final result.
0
Steve HoggITCommented:
>--I cant do the order by in this part because it is a subquery
>Select * from TableProducts
>where ParentNumber IN (select ProductId from tmpTable1)

In this query above it does not matter what order the ProductID is found in the tmpTable1, the order has no bearing on that query.

0
lundnakCommented:
See the following code snippet for the solution you are seeking.  In SQL 2005 you can have an order by in a subquery, but you need to include the "top" command.

declare @rowcnt int
select @rowcnt = count(*) from tmpTable1
 
Select * from TableProducts
where ParentNumber IN (select top (@rowcnt) ProductId from tmpTable1 order by ProductID)
 
-- I agree with the previous posts, you don't need to worry about the order in a subquery

Open in new window

0
Anthony PerkinsCommented:
>> In SQL 2005 you can have an order by in a subquery<<
Actually it was there in SQL Server 2000 and SQL Server 7.  There was no TOP in 6.5 so it was not allowed then.
0
lundnakCommented:
Thanks...  in SQL 2005 they added TOP (@cnt).
0
Anthony PerkinsCommented:
So?  You stated "In SQL 2005 you can have an order by in a subquery, but you need to include the "top" command."  You could do that in SQL Server 2000 and 7.
0
puranik_pCommented:
How about this....
Select * from TableProducts tp
INNER JOIN tmpTable1 t
ON (tp.ParentNumber = t.ProductId)
ORDER BY t.ProductId

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aplusexpertCommented:
Please try below code:


DECLARE @tmpTable1 TABLE( ProductId nvarchar(32) )

insert into @tmpTable1 SELECT AddressID FROM Address ORDER BY Address.AddressID

select * From @tmpTable1
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.