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?
 
puranik_pConnect With a Mentor Commented:
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
 
zwseemmCommented:
The order is controlled by the table you are inserting the data into.
0
 
HoggZillaCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
 
HoggZillaCommented:
>--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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.