Creating a temp table for a lot of data 1

[SyntaxTerror]

I'm writing a complicated script requiring a lot of logic, to the point that I can't make all of the calculations in a single statement. I got to the point where I had something like 5 nested subqueries, SQL wasn't liking it, and my coworker said I was overcomplicating it (the logic of the subqueries made sense conceptually, but SQL wouldn't allow what I was doing...)

We ended up dumping some of the results into a temp table. My coworker wrote it and basically just created the table, inserted the same number of null values as I'd have columns (around 10 values), then updated the column with the results of the calculations in a bunch of statements.

Well, I've found that I need to make ANOTHER temp table to dump more information into (or somehow modify the one I have). The table would need a column for Inventory ID, Site ID, and Inventory Quantity. I don't know how to make a table where I don't specify the datatypes and insert a null value as a placeholder for each piece of data.

e.g.

INSERT INTO #temp_counts
SELECT	SiteID,
		NULL,
		NULL,
		NULL,
		NULL,
		NULL,
		NULL,
		NULL,
		NULL,
		NULL,
		NULL,
		NULL,
		NULL

Perhaps I'm misunderstanding and I'm sorry if I haven't provided sufficient information. I'm happy to answer any additional questions. Thank you.

 

[gmmastros]

Create 
Table  #Temp
       (InventoryId Int,
       SiteID Int,
       InventoryQuantity Decimal(10,2),
       OtherColumn VarChar(20)
       )

Then, when you want to put data in to the temp table...

Insert Into #Temp(InventoryId, SiteId, InventoryQuantity)
Select InventoryId, SiteID, InventoryQuantity
From   SomeTable
Where  ....

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SyntaxTerror]

Okay, so all you're doing is creating the columns, yes? The number of rows are irrelevant? The temp table I'm using now only has a single row, so I wasn't sure.

 

[gmmastros]

Yes. The number of rows should be irrelevant.

Are you suggesting that there should be 1 row in this table? If so... why have a temp table with just a single row? That doesn't make sense to me. I mean... I can understand a permanent table with just one row so that you can keep that data in the table. I don't understand a temp table with just one row, especially since you can store the data in variables instead.

If you want, you can post all the code and we'll take a look and offer suggestions.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SyntaxTerror]

The reason the temp table I'm currently using only has one row is because my report was getting too unruly and I was making too many calculations for a single statement. I was getting to the point where I needed to perform aggregate functions on nested subqueries. By dumping it into the table, I can do that. ...but perhaps you know a better way.

Currently, and this is the reason I'm wanting to create a 2nd temp table (or update the one I have...), I have a statement that would end up being around 6 nested subqueries if SQL Server would even let me do that.

What I think I'm going to end up having to do is update the temp table with the results, then pull the results in a new statement and do further calculations, then update the table with those results, and so on until I have results I can do the final calculations on.

 

[gmmastros]

Why don't you use variables instead?

For example, if you have a temp table with InventoryId, SiteId, and InventoryQuantity, and there is just a single row, then you may be able to use variables instead. Something like this:

Declare @InventoryId int,
        @SiteId Int,
        @InventoryQuantity Decimal(10,2)

Select  @InventoryId = SomeColumn,
        @SiteId = OtherColumn,
        @InventoryQuantity = Whatever
From    SomeTable

-- More calculations.

Select  @InventoryQuantity = @InventoryQuantity + OtherColumn
From    OtherTable
Where   OtherTable.InventoryId = @InventoryID

-- Return results
Select  @InventoryId As InventoryId,
        @InventoryQuantity As InventoryQuantity,
        @SiteId As SiteId

I may be "barking up the wrong tree" on this, but it seems to me (based on your previous comments) that using variables may be easier (and may even perform better).

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SyntaxTerror]

Thank you!!! As I continue learning SQL, I find that my inability to write what I want to write often stems from simply not knowing my options. I think I'll try to apply the use of variables here.

As always, you and others here never cease to impress me with the promptness and helpfulness of your answers.

 

[SyntaxTerror]

Okay, to be more specific, take a look at this code. Do you think I could use variables to store only the values pulled from the subquery below? (I've changed the column names to make everything more obvious)

SELECT InventoryID, Sum(Quantity)Qty, SiteID
FROM PurchaseOrderTransactions
WHERE CreatedDate BETWEEN (SELECT Min(Date) FROM #temp) AND (SELECT Max(Date) FROM #temp) AND InventoryID NOT LIKE '%NonStock%'
GROUP BY SiteID, InventoryID
ORDER BY SiteID, InventoryID

 

[gmmastros]

yes. Like this:

Declare @MinDate DateTime,
        @MaxDate DateTime

Select  @MinDate = Min(Date),
        @MaxDate = Max(Date)
From    #Temp

SELECT InventoryID, Sum(Quantity)Qty, SiteID
FROM   PurchaseOrderTransactions
WHERE  CreatedDate BETWEEN @MinDate And @MaxDate
       AND InventoryID NOT LIKE '%NonStock%'
GROUP BY SiteID, InventoryID
ORDER BY SiteID, InventoryID

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SyntaxTerror]

I'm sorry for any confusion. I meant that ENTIRE query is a subquery. It would be nested within a nested query. I'm not sure if I can use variables for that kind of thing.

I guess, instead of having it all in one query, I'd have a separate select, from, where, etc. for each variable, yeah?

 

[gmmastros]

I can't really answer your question because it depends on the data.

Variables can only store one piece of information. For example, @InventoryID could be the value 7. That's it.

The query you show makes it seem like it could return multiple rows. For example, one row could have InventoryId = 7 and another row with InventoryId = 12. If there can be multiple rows, then you cannot use variables.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SyntaxTerror]

Yep, there are many rows of inventory IDs, so I'll have to use a temp table.

It's good to know that I always have variables as an option though

Thanks again.

 

[kjv1611]

If a variable would work best (I've not looked at the whole thing, just quick skim), then you could look at using a Table VAR just like you could have a single value VAR.

See a couple of references to see if it'll help. In some circumstances, a Table variable works out better than a temp table... and vice versa in other circumstances:

http://odetocode.com/code/365.aspx

http://www.sqlteam.com/article/using-table-variables

Table Variable vs Temp Table:

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx