Geek Answers Handbook

WHERE-Clause reuse subquery result for INSERT

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

My query looks like this:

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:

This is right 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 from [a] (= MAT_WS_ID). This seems to give the correct results. But I do not understand why!

I mean, MAT_WS_ID is part of both X_ALIAS and Y_ALIAS temporary tables. Therefore, in the SELECT statement for [b], in the query area of ​​the [b] -select query, MAT_WS_ID can only be known from the Y_ALIAS table. (Or am I mistaken, I'm more C ++, maybe the sphere objects in SQL and C ++ are completely different)

I just don't know how best to use SQL Server to reuse the result of selecting a scalar. Or do I just not need to copy the selection for each column, and the sql server optimizes it myself?

+1
source share
2 answers

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
+2
source

You can rank the rows in #TempTableX and #TempTableY , breaking them into MAT_RM_NAME in the first and MAT_WS_ID in the last, then use regular joins with filtering rownum = 1 in both tables ( rownum is a 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 ORDER BY (SELECT 1) is a trick to indicate an undefined ordering that will result in an undefined rownum = 1 rows selected by the query. This more or less duplicates your TOP 1 without explicit order, but I would recommend that you specify a more reasonable ORDER BY to make the results more predictable.

0
source

More articles:


All Articles