inserting cte table into temp table?

Question

Post reply

inserting cte table into temp table?

mick burden

SSCarpal Tunnel

Points: 4432
More actions
November 4, 2013 at 6:13 am

#285477

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.*;
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]

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

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: 11543 More actions · Answer 2

-- 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 More actions · Answer 3

mick burden

SSCarpal Tunnel

Points: 4432

November 4, 2013 at 7:30 am

#1663815

Thanks Keith, that works a treat

mick burden SSCarpal Tunnel Points: 4432 More actions · Answer 4

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: 11543 More actions · Answer 5

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 More actions · Answer 6

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: 1004356 More actions · Answer 7

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.

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

mick burden SSCarpal Tunnel Points: 4432 More actions · Answer 8

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: 1004356 More actions · Answer 9

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.

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

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 10

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: 1004356 More actions · Answer 11

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.

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

mister.magoo SSC-Forever Points: 47174 More actions · Answer 12

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

mick burden SSCarpal Tunnel Points: 4432 More actions · Answer 13

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