Deciphering SQL query

Question

I am currently reviewing a query without access to the databases on which the query is performed. (It's not ideal but that's what I am tasked with). I am not a SQL expert and trying to identify what the below code does as I cannot run the query. It is reading from and writing to the same temp table (duplicating?). I don't know what the source of 'Y' is or what the end result is. Any help is appreciated. Thank you.

INSERT INTO #temp1
    SELECT X.CURSTATUS ,X.GENDER ,Y.PACKAGE ,X.AGE ,1 AS factor1 ,1 AS factor2;
FROM #temp1 X WITH (NOLOCK) ,
     ( SELECT 'P1' AS PACKAGE UNION ALL SELECT 'P2' ) Y
WHERE X.PACKAGE = 'P5';

score 2 · Accepted Answer · answered Aug 03 '17 at 19:05

It is not really writing to the same table. It is "appending" rows to the same table. That is, existing data in the table is not affected.

What it is doing is adding rows for packages "P1" and "P2" for all "P5" packages. This adds the new rows to the table; the "P5" row remains.

score 2 · Answer 2 · answered Aug 03 '17 at 19:35

For every row in #temp that has a PACKAGE value of "P5", the query is inserting two new rows with PACKAGE values of "P1" & "P2" respectivlly.

Reformatting the query and replacing obsolete syntax with modem syntax should make it easier to understand.

INSERT INTO #temp1 (CURSTATUS,  GENDER, PACKAGE, AGE, factor1, factor2)
SELECT 
    X.CURSTATUS,
    X.GENDER,
    Y.PACKAGE,
    X.AGE,
    1 AS factor1,
    1 AS factor2
FROM 
    #temp1 X
    CROSS JOIN ( 
                SELECT 'P1' AS PACKAGE 
                UNION ALL 
                SELECT 'P2' 
                ) Y
WHERE 
    X.PACKAGE = 'P5';

score 1 · Answer 3 · answered Aug 03 '17 at 19:19

INSERT INTO #temp1

-- this is where that data is being inserted into. It should BTW have columns explicitly defined, this format is a SQL antipattern

    SELECT X.CURSTATUS ,X.GENDER ,Y.PACKAGE ,X.AGE ,1 AS factor1 ,1 AS factor2;
FROM #temp1 X WITH (NOLOCK) ,

-- this is selecting the current rows from #temp

     ( SELECT 'P1' AS PACKAGE UNION ALL SELECT 'P2' ) Y

--Y is a two record table with one column called package, since there is no specific join shown, it is a cross join - Again an antipattern, it is far better to explicitly use the Cross Join keywords to make it clear what is going on.

WHERE X.PACKAGE = 'P5';

-- this filters the records from #temp to grab only those where the record values is 'P5'. Since it cross joins to the Two record table Y, it takes the data in the other columns for the P% records and inserts new records for P1 and P2. If you have ten P5 records, this insert would insert 10 P1 records and 10 P2 records.

Deciphering SQL query

3 Answers3