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

Creating an SQL Temp Table? Expand / Collapse
Author
Message
Posted Tuesday, March 5, 2013 1:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 21, 2013 8:06 AM
Points: 21, Visits: 48
Hi guys,

I had reached out to everyone earlier today to ask for help, and I was able to take a lot away from that, so thank you in advance.

I'm looking to turn the query that I created into a "Temp Table".

I've searched far and wide on the internet and can find little to no literature on it.

Can anyone help me get started with creating a temp table?

The previous query was as follows:

SELECT A.Portfolio, B.PortfolioID, SUM(B.MarketValue) AS SumOfMV,
COUNT(B.SecID) AS [# of Securities]
From ERTutAccounts A
INNER JOIN ERTutPositions B ON A.PortfolioID = B.PortfolioID
GROUP BY A.Portfolio, B.PortfolioID
HAVING COUNT(B.SecID) > 70 OR SUM(B.MarketValue) > 30000000

Before attempting to build the above query into a temp table, I simply attempted a really trivial table to no avail. Can someone tell me what I'm doing wrong and the right place to begin?

CREATE TABLE #Test
(PortfolioID int,
Portfolio varchar(200))

INSERT INTO #Test (PortfolioID, Portfolio)
SELECT PortfolioID, Portfolio
FROM ERTutAccounts
WHERE PortfolioID = 1
Post #1427016
Posted Tuesday, March 5, 2013 1:20 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,062, Visits: 11,891
What is the issue you are having? The query you posted looks like it should work fine, assuming the base table exists.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1427022
Posted Tuesday, March 5, 2013 1:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 21, 2013 8:06 AM
Points: 21, Visits: 48
Hi Sean,

The query itself works fine, I'm having an issue converting this into a temp table. I'm not even quite sure how temp tables work in theory, but I'm attempting to convert the functional query into a temp table.
Post #1427024
Posted Tuesday, March 5, 2013 1:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,062, Visits: 11,891
meadow0 (3/5/2013)
Hi Sean,

The query itself works fine, I'm having an issue converting this into a temp table. I'm not even quite sure how temp tables work in theory, but I'm attempting to convert the functional query into a temp table.


I don't think I understand what you mean about converting a query into a temp table. Temp tables work just like a permanent table but they will go away when the connection it closed, or it is dropped.

You could have your original query generate and populate a temp table if you want quite easily.

SELECT A.Portfolio, B.PortfolioID, SUM(B.MarketValue) AS SumOfMV,
COUNT(B.SecID) AS [# of Securities]
INTO #YourTempTable
From ERTutAccounts A
INNER JOIN ERTutPositions B ON A.PortfolioID = B.PortfolioID
GROUP BY A.Portfolio, B.PortfolioID
HAVING COUNT(B.SecID) > 70 OR SUM(B.MarketValue) > 30000000

If you execute this you will now have a temp table called #YourTempTable that has the results of your above query. You can now treat it like any other table for the duration of your process.

--EDIT--
Misplaced quotes.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1427027
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse