Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

inserting cte table into temp table? Expand / Collapse
Author
Message
Posted Monday, November 4, 2013 6:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:54 AM
Points: 287, Visits: 403
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.*;


Post #1511072
Posted Monday, November 4, 2013 7:10 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 9:14 PM
Points: 564, Visits: 851
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
Post #1511090
Posted Monday, November 4, 2013 7:26 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:44 AM
Points: 1,293, Visits: 1,427


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



Post #1511102
Posted Monday, November 4, 2013 7:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:54 AM
Points: 287, Visits: 403
Thanks Keith, that works a treat
Post #1511104
Posted Monday, November 4, 2013 7:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:54 AM
Points: 287, Visits: 403
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


Post #1511107
Posted Monday, November 4, 2013 7:33 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:44 AM
Points: 1,293, Visits: 1,427
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;

Post #1511110
Posted Monday, November 4, 2013 7:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:54 AM
Points: 287, Visits: 403
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
Post #1511112
Posted Monday, November 4, 2013 7:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 36,749, Visits: 31,197
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1511119
Posted Monday, November 4, 2013 8:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:54 AM
Points: 287, Visits: 403
thanks Jeff, it's not something I've used before. What is the advantage? and can the table created be read using VB.net?
Post #1511120
Posted Monday, November 4, 2013 11:20 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 36,749, Visits: 31,197
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1511214
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse