• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

Populate a temporary table with data ordered randomly???

Okay,

I have a stored procedure. In this stored procedure I have created a temporary table..

...Now I need to populate this temp table with data from another table. BUT I need to randomly order the data before populating the temp table?? Can anyone help with this. My simple stored procedure looks like this:

CREATE PROCEDURE sp_CreateRestTable

AS

CREATE TABLE #temp_tbl_Restaurants (
RestID int,
RestName varchar(50),
ImageURL varchar(50) )


INSERT INTO #temp_tbl_Restaurants (RestID, RestName, ImageURL)  (SELECT RestID, RestName, ImageURL FROM tbl_Restaurants  ORDER BY NEWID() )

GO

Trouble is the insert clause doesn't like the order by part??? Any help would be greatly appreciated!!

Joe
0
swerver69
Asked:
swerver69
  • 3
  • 2
1 Solution
 
DonKronosCommented:
Since there is no concept of order in a relational table why are you trying to order the inserts????
0
 
swerver69Author Commented:
Thanx for your reply..........My delimma is..........

I have to display a whole bunch of images on a webpage randomly built in asp.net vb. The only real way I can do this is with a datalist and the datalist object does not allow paganation.
I can write my SELECT statement to generate the dataset randomly using the ORDER BY NEWID() clause.

I've written a routine to allow kinda psuedo pagination of my datalist. But to do this I have to rebind the data everytime the function is called. So if you click on "page two" instead of getting the next ten records from my randomly generated dataset, I get ten MORE records because the dataset gets rebuilt.

Thats why I had the idea of creating a temp table first and scrabbling the records there. Then I would have a stable table to generate my dataset from..

Any ideas???
0
 
Scott PletcherSenior DBACommented:
The extra parentheses may be "confusing" SQL, please try this:


INSERT INTO #temp_tbl_Restaurants (RestID, RestName, ImageURL)  
SELECT RestID, RestName, ImageURL
FROM tbl_Restaurants  
ORDER BY NEWID()
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
swerver69Author Commented:
ScottPletcher,

Thanx, that worked. Can you help me further (or if not I'll happily award you the points and post again). Works just fine if I use the query analyser.....

Now I can't seem to trigger the stored procedure to fire (regularly) in my page code. I've got a button_click event that is supposed to fire the stored procedure and thus create the temp table. For now, in testing I'm simply creating a new table (the temp part will come later)... I guess I need some catch or something.

Anyway, I've built a sub routine and wired the routine to fire on button click if login credentials match:

.........................................................

      Sub CreateTempTable()

            
            Dim strConnString as String = System.Configuration.ConfigurationSettings.AppSettings("DBConn")
            Dim objConn as New SqlConnection(strConnString)
            Const strSql as String = "sp_CreateRestTable"
            Dim objCmd as New SqlCommand(strSql, objConn)
            objCmd.CommandType = CommandType.StoredProcedure
            objConn.Open()

            ObjConn.Close()
            
      
      End Sub

................................................................................


..but its hit or miss. Can't seem to get it to work in a regular way. I'm sure my Sub () leaves alot to be desired. I don't know. What do you think??
0
 
Scott PletcherSenior DBACommented:
Sorry, I would help if I could, but I'm now just a SQL guy, not a VB/pgmr type any more :-( .
0
 
swerver69Author Commented:
jeez, Im an idiot.................don't know how I missed that..........thanx
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now