Reuse subquery result in WHERE-Clause for INSERT

Question

i am using Microsoft SQL Server 2008 i would like to save the result of a subquery to reuse it in a following subquery. Is this possible? What is best practice to do this? (I am very new to SQL)

My query looks like:

INSERT INTO [dbo].[TestTable]
(
[a]
,[b]
)
SELECT 
(
   SELECT TOP 1 MAT_WS_ID  
   FROM #TempTableX AS X_ALIAS 
   WHERE OUTERBASETABLE.LT_ALL_MATERIAL = X_ALIAS.MAT_RM_NAME
)  
,(
    SELECT TOP 1 MAT_WS_NAME
    FROM #TempTableY AS Y_ALIAS
    WHERE Y_ALIAS.MAT_WS_ID = MAT_WS_ID 
    --( 
        --SELECT TOP 1 MAT_WS_ID  
        --FROM #TempTableX AS X_ALIAS 
        --WHERE OUTERBASETABLE.LT_ALL_MATERIAL = X_ALIAS.MAT_RM_NAME
    --)
) 
FROM [dbo].[LASERTECHNO]  AS OUTERBASETABLE

My question is:

Is this correct what i did. I replaced the second SELECT Statement in the WHERE-Clause for [b] (which is commented out and exactly the same as for [a]), with the result of the first SELECT Statement of [a] (=MAT_WS_ID). It seems to give the right results. But i dont understand why!

I mean MAT_WS_ID is part of both temporary tables X_ALIAS and Y_ALIAS. So in the SELECT statement for [b], in the scope of the [b]-select-query, MAT_WS_ID could only be known from the Y_ALIAS table. (Or am i wrong, i am more a C++, maybe the scope things in SQL and C++ are totally different)

I just wannt to know what is the best way in SQL Server to reuse an scalar select result. Or should i just dont care and copy the select for every column and the sql server optimizes it by its own?

you should always join onto a table and remove inner selects for better performance — Clinton Ward, Aug 05 '12 at 16:36

score 2 · Answer 1 · answered Aug 05 '12 at 17:27

2

One approach would be outer apply:

SELECT  mat.MAT_WS_ID
,       (
        SELECT TOP 1 MAT_WS_NAME
        FROM #TempTableY AS Y_ALIAS
        WHERE Y_ALIAS.MAT_WS_ID = mat.MAT_WS_ID  
        ) 
FROM    [dbo].[LASERTECHNO]  AS OUTERBASETABLE
OUTER APPLY
        (
        SELECT  TOP 1 MAT_WS_ID  
        FROM    #TempTableX AS X_ALIAS 
        WHERE   OUTERBASETABLE.LT_ALL_MATERIAL = X_ALIAS.MAT_RM_NAME
        ) as mat

answered Aug 05 '12 at 17:27

Andomar

232,371
49
380
404

And if there are more than one of this similar statements? – grimblegrumble Aug 07 '12 at 10:17
That would depend on the kind of similarity. One option is mulitple `outer apply`, but there are other options, like [`with`](http://msdn.microsoft.com/en-us/library/ms175972.aspx) – Andomar Aug 07 '12 at 15:04

score 0 · Answer 2 · answered Aug 05 '12 at 20:32

You could rank rows in #TempTableX and #TempTableY partitioning them by MAT_RM_NAME in the former and by MAT_WS_ID in the latter, then use normal joins with filtering by rownum = 1 in both tables (rownum being the column containing the ranking numbers in each of the two tables):

WITH x_ranked AS (
  SELECT
    *,
    rownum = ROW_NUMBER() OVER (PARTITION BY MAT_RM_NAME ORDER BY (SELECT 1))
  FROM #TempTableX
),
y_ranked AS (
  SELECT
    *,
    rownum = ROW_NUMBER() OVER (PARTITION BY MAT_WS_ID ORDER BY (SELECT 1))
  FROM #TempTableY
)
INSERT INTO dbo.TestTable (a, b)
SELECT
  x.MAT_WS_ID,
  y.MAT_WS_NAME
FROM dbo.LASERTECHNO t
  LEFT JOIN x_ranked x ON t.LT_ALL_MATERIAL = x.MAT_RM_NAME AND x.rownum = 1
  LEFT JOIN y_ranked y ON x.MAT_WS_ID       = y.MAT_WS_ID   AND y.rownum = 1
;

The ORDER BY (SELECT 1) bit is a trick to specify an indeterminate ordering, which, accordingly, would result in indeterminate rownum = 1 rows picked by the query. That is to more or less duplicate your TOP 1 without an explicit order, but I would recommend you to specify a more sensible ORDER BY clause to make the results more predictable.

Reuse subquery result in WHERE-Clause for INSERT

2 Answers2

Linked