It is important to understand that there are two types of temporary table:
#Table with a hashtag means a table that is actually written to disk.
@Table with an @ means a table that is created in memory only.
Both types have their own pros and cons. SELECT INTO only works with #Table. The main advantage of SELECT INTO is that you do not need to declare/create the temporary table first. The SELECT clause will determine the fields and data types in the temporary table.
So for example:
SELECT i.ItemCode INTO #ITEMCODES
FROM OITM i
WHERE i.ItmsGrpCod = 10
Will create a temporary table with field ItemCode NVARCHAR(50), and fill this table with all ItemCodes from Item Group 10.
Next I can use this temporary table in a followup query:
SELECT c.CardCode, c.CardName, cat.Substitute
FROM OCRD c
INNER JOIN OSCN cat ON c.CardCode = cat.CardCode
WHERE cat.ItemCode IN (select * from #ITEMCODES)
Alternatively you could use the temporary table in another application all together, as it is written to disk, and thus available to any application that has access to the database.
However, every time you run the query, you will need to drop the temporary table or empty it.
You can do the same thing with a table in memory, but it is more complicated:
DECLARE @ITEMCODES TABLE (ItemCode NVARCHAR(50))
INSERT INTO @ITEMCODES (ItemCode)
(SELECT i.ItemCode
FROM OITM i
WHERE i.ItmsGrpCod = 10)
The @ITEMCODES table can then be usd in a followup query in the same manner, but it cannot be used outside the scope of this query.
Any ideas or situations where this makes sense? I have used the into statement for inserts but thinking of rewriting some of my larger queries to see if they would run faster.
Bluesky
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.