?
Solved

Inserting Data Into a Temp Table

Posted on 2008-11-08
16
Medium Priority
?
1,123 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:doctor069
  • 3
  • 2
  • 2
  • +5
15 Comments
 
LVL 6

Expert Comment

by:zwseemm
ID: 22914637
The order is controlled by the table you are inserting the data into.
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22914669
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
 

Author Comment

by:doctor069
ID: 22914707
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 33

Expert Comment

by:hongjun
ID: 22914752
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
 
LVL 6

Expert Comment

by:zwseemm
ID: 22914802
Put it in the table without the order by and then use the order by when you select it back out.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22914825
>>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
 

Author Comment

by:doctor069
ID: 22914859
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
 
LVL 33

Expert Comment

by:hongjun
ID: 22914863
You should be concern with the final result of Select * From TableProducts isn't it?
Do a order by on your final result.
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22915067
>--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
 
LVL 7

Expert Comment

by:lundnak
ID: 22915151
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22917395
>> 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
 
LVL 7

Expert Comment

by:lundnak
ID: 22918319
Thanks...  in SQL 2005 they added TOP (@cnt).
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22918919
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
 
LVL 14

Accepted Solution

by:
puranik_p earned 1000 total points
ID: 22948233
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
 
LVL 7

Expert Comment

by:aplusexpert
ID: 25569123
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question