Creating an SQL Temp Table?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Creating an SQL Temp Table?

Creating an SQL Temp Table?

Rate Topic

Display Mode

Topic Options

Author

Message

meadow0

Posted Tuesday, March 05, 2013 1:04 PM

Grasshopper

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

Sean Lange

Posted Tuesday, March 05, 2013 1:20 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 3:12 PM
Points: 8,957, Visits: 8,523

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

Post #1427022

meadow0

Posted Tuesday, March 05, 2013 1:23 PM

Grasshopper

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

Sean Lange

Posted Tuesday, March 05, 2013 1:29 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 3:12 PM
Points: 8,957, Visits: 8,523

meadow0 (3/5/2013)

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

Post #1427027

« Prev Topic | Next Topic »

Permissions