Populate a temporary table with data ordered randomly???

Posted on 2006-04-26
Last Modified: 2008-03-17

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 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() )


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

Question by:swerver69
    LVL 15

    Expert Comment

    Since there is no concept of order in a relational table why are you trying to order the inserts????

    Author Comment

    Thanx for your reply..........My delimma is..........

    I have to display a whole bunch of images on a webpage randomly built in 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???
    LVL 68

    Accepted Solution

    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  

    Author Comment


    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

          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??
    LVL 68

    Expert Comment

    Sorry, I would help if I could, but I'm now just a SQL guy, not a VB/pgmr type any more :-( .

    Author Comment

    jeez, Im an idiot.................don't know how I missed that..........thanx

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now