inserting cte table into temp table?

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    Hi all, I've been using the following code in a SP that get's 50 random rows from a table, is there a way I can insert the data into a temporary table from within the SP?

    'USE [misc]

    'GO

    '/****** Object: StoredProcedure [dbo].[sp_50NewPostcodes] Script Date: 09/19/2013 19:04:40 ******/

    'SET ANSI_NULLS OFF

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    create PROCEDURE [dbo].[sp_50NewPostcodes]

    AS

    ;

    with cte as

    ( SELECT TOP 50 * FROM postcodes TABLESAMPLE (0.1 percent) WITH (ROWLOCK, UPDLOCK)

    where Streets is null ORDER BY newid())

    update cte SET streets = 'Picked up'

    output inserted.*;

  • Keith Tate

    SSCrazy Eights

    Points: 9884

    You just need to create the temp table in the stored procedure and then use the output...into temp table. Like so:

    create table #TempTable (same strucutre as postcodes?)

    with cte as

    ( SELECT TOP 50 * FROM postcodes TABLESAMPLE (0.1 percent) WITH (ROWLOCK, UPDLOCK)

    where Streets is null ORDER BY newid())

    update cte SET streets = 'Picked up'

    output inserted.*

    into #tempTable

    I would also get in the habit of listing out your column names and using 2-part naming for your objects.



    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Tom257

    SSChampion

    Points: 11475

    -- a normal temp table does not work

    CREATE PROC TEST

    AS

    SELECT 'Test' AS Col1, 'Row' AS Col2

    INTO #TEST ;

    GO

    -- now run the proc

    EXEC TEST;

    -- and test the temp table

    -- does not exist - Invalid object name #TEST

    SELECT * FROM #TEST; -- ERRORS

    GO

    -- The way to get around this is to use a Global Temp table using ## syntax

    CREATE PROC TESTIT

    AS

    SELECT 'Test' AS Col1, 'Row' AS Col2

    INTO ##TESTIT ;

    GO

    -- now run the proc

    EXEC TESTIT;

    -- and test the temp table

    SELECT * FROM ##TESTIT;

    -- but you can't re-run it

    EXEC TESTIT; -- ERRORS

    --There is already an object named '##TESTIT' in the database.

    -- So check exists and Delete before re-running.

    IF OBJECT_ID('tempdb..##TESTIT') IS NOT NULL

    DROP TABLE ##TESTIT;

    EXEC TESTIT

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    Thanks Keith, that works a treat

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    As Tom suggests, temp tables seem not to work so I've created and dropped a normal table, I've posted my complete code below incase it'll help anyone else.

    USE [misc]

    GO

    /****** Object: StoredProcedure [dbo].[sp_50NewPostcodes] Script Date: 11/04/2013 10:28:34 ******/

    SET ANSI_NULLS OFF

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    Alter PROCEDURE [dbo].[sp_50NewPostcodestemp]

    AS

    IF OBJECT_ID('Results') IS NOT NULL DROP TABLE Results

    CREATE table Results(

    postcode varchar(10),streets varchar(2000))

    ;

    with cte as

    ( SELECT TOP 50 postcode,streets FROM postcodes TABLESAMPLE (0.1 percent) WITH (ROWLOCK, UPDLOCK)

    where Streets is null ORDER BY newid())

    update cte SET streets = 'Picked up'

    output inserted.*

    into Results

  • Tom257

    SSChampion

    Points: 11475

    Or you could create the temp table outside the proc, before calling the proc.

    CREATE PROC TEST3

    AS

    -- Proc refers to a temp table that must exist prior to calling.

    INSERT INTO #TEST3

    SELECT 'InProcTest', 'InProcRow';

    GO

    -- Create the temp table ahead of time calling the proc

    SELECT 'Original Test' AS Col1, 'Original Row' AS Col2

    INTO #TEST3 ;

    -- run the proc

    EXEC TEST3;

    -- Check results

    SELECT * FROM #TEST3;

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    Thanks again Tom, this project is part of a VB.Net project to get a random x amount of postcodes from an SQL Server table and insert the records into an Access database while using a Stored Proc, it seems a "dirty" way of doing it, but it works

  • Jeff Moden

    SSC Guru

    Points: 996830

    It would probably make your whole life a lot easier if you changed the stored procedure to an iTVF (inline Table Valued Function) because then you could easily create a Temp Table from it as follows...

    SELECT *

    INTO #SomeTempTable

    FROM dbo.NewFunction()

    ;

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    thanks Jeff, it's not something I've used before. What is the advantage? and can the table created be read using VB.net?

  • Jeff Moden

    SSC Guru

    Points: 996830

    mick burden (11/4/2013)


    thanks Jeff, it's not something I've used before. What is the advantage? and can the table created be read using VB.net?

    The biggest advantage is that you can use SELECT/INTO instead of having to precreate a Temp Table. The advantages there are that you don't have to select all columns and SELECT/INTO is incredibly fast because it uses minimal logging. Like a stored procedure, it also allows you to pass in parameters and you can actually think of the function as a parameterized view.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Kurt W. Zimmerman

    SSCrazy Eights

    Points: 8676

    Jeff Moden (11/4/2013)


    mick burden (11/4/2013)


    thanks Jeff, it's not something I've used before. What is the advantage? and can the table created be read using VB.net?

    The biggest advantage is that you can use SELECT/INTO instead of having to precreate a Temp Table. The advantages there are that you don't have to select all columns and SELECT/INTO is incredibly fast because it uses minimal logging. Like a stored procedure, it also allows you to pass in parameters and you can actually think of the function as a parameterized view.

    The only drawback that I see is there isn't any index which would have to be created separately. I've used this method as long as I wasn't joining it with other tables.... It makes for a very inefficient processing....

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • Jeff Moden

    SSC Guru

    Points: 996830

    Kurt W. Zimmerman (11/4/2013)


    Jeff Moden (11/4/2013)


    mick burden (11/4/2013)


    thanks Jeff, it's not something I've used before. What is the advantage? and can the table created be read using VB.net?

    The biggest advantage is that you can use SELECT/INTO instead of having to precreate a Temp Table. The advantages there are that you don't have to select all columns and SELECT/INTO is incredibly fast because it uses minimal logging. Like a stored procedure, it also allows you to pass in parameters and you can actually think of the function as a parameterized view.

    The only drawback that I see is there isn't any index which would have to be created separately. I've used this method as long as I wasn't joining it with other tables.... It makes for a very inefficient processing....

    Kurt

    For me, those problems normally don't exist and they probably shouldn't exist for most. Stop and think about what should be in a Temp Table even if you do have to join against it. It should ONLY contain data that will actually be used meaning that a table scan on a Temp Table is frequently as fast or faster than the use of indexes. If your temp table is so large that you need indexes on it to gain performance, then you may have simply put the wrong data into the Temp Table.

    To wit, indexing a 50 row table like what the OP is producing is highly likely to be counter productive.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • mister.magoo

    SSC-Forever

    Points: 47068

    Mick, can I ask why you want to insert the 50 rows into a temp table?

    Can't you just take the output from the "OUTPUT" clause and use that directly in your VB code?

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    Sorry I've only just replied to your posts as I've been on leave. the reason it was only 50 on my example is that it was an existing SP which initially was used to send data to a WCF program which at that time would only handle small amounts of data. With examples you all have been showing me I upped the figure from 50 to 5000 and I'm now using the data to populate an Access database instead. It's also something I could be using for other projects in the future. By using your submissions I've been also been learning more abour SQL server which I find immensely interesting. As for Mister.Magoo's about using the OUTPUT statement, I'd never thoughts of that, tried it, and it works, thanks. Thanks to everyone who contibuted to my problem

  • Viewing 14 posts - 1 through 14 (of 14 total)

    You must be logged in to reply to this topic. Login to reply