none
Query HELP : Dynamic TSql statement using Union

    Question

  • Hi All

    I want to UNION the resultset from 2 dynamic T-SQL statements on SQL 2000.

    I have to calculate the sum of Amount for each serial.

    I modified the query to use Dynamic SQL as the code is bit complex.

    begin
    
    DECLARE @Sql_A NVARCHAR(2000)
    DECLARE @Sql_B NVARCHAR(2000)
    
    
    Insert into #temp
    
    SELECT [SERIAL], 'AMOUNT' = SUM(AMOUNT)
    FROM 
    (
    		
    set @Sql_A  = '(SELECT ''SERIAL'' = SERIAL_A, ''AMOUNT'' = AMOUNT 
    FROM [dbo].[TableA] )'
    
    UNION ALL
    
    set @Sql_B = '( SELECT 'SERIAL' = SERIAL_B, 'AMOUNT' = AMOUNT 
    FROM [dbo].[TableB] )'
    
    	
    )
    AS A GROUP BY [SERIAL] 
    
    end

    I dont get it rite even If i use it like exec (@Sql_A+ UNION + @Sql_B)

    Please advise me the best way to group the dynamic Tsql statments.

    Create table TableA (Serial_A int, Amount float)
    Go
    
    INSERT INTO TableA (Serial_A ,Amount) VALUES('1','10')
    INSERT INTO TableA (Serial_A ,Amount) VALUES('1','10')
    INSERT INTO TableA (Serial_A ,Amount) VALUES('2','10')
    INSERT INTO TableA (Serial_A ,Amount) VALUES('2','10')
    INSERT INTO TableA (Serial_A ,Amount) VALUES('3','10')
    Go
    
    
    Create table TableB (Serial_B int, Amount float)
    Go
    
    INSERT INTO TableB (Serial_B ,Amount) VALUES('1','20')
    INSERT INTO TableB (Serial_B ,Amount) VALUES('1','15')
    INSERT INTO TableB (Serial_B ,Amount) VALUES('2','5')
    INSERT INTO TableB (Serial_B ,Amount) VALUES('3','10')
    INSERT INTO TableB (Serial_B ,Amount) VALUES('4','10')
    Go
    
    
    
    Create table #temp
    (
    Serial int, Amount float
    )
    
    
    begin
    
    Insert into #temp
    
    SELECT [SERIAL], 'AMOUNT' = SUM(AMOUNT)
    FROM 
    (
    		
    SELECT 'SERIAL' = SERIAL_A, 'AMOUNT' = AMOUNT 
    FROM [dbo].[TableA] 
    
    UNION ALL
    
    SELECT 'SERIAL' = SERIAL_B, 'AMOUNT' = AMOUNT 
    FROM [dbo].[TableB] 
    	
    )
    AS A GROUP BY [SERIAL] 
    
    end
    
    SELECT * FROM #temp
    drop table #temp


    Thanks a lot in advance.






    • Edited by xXShanXx Thursday, March 27, 2014 4:20 PM
    Thursday, March 27, 2014 4:15 PM

Answers

  • You need to append to a variable and execute the dynamic sql as below:

    Drop table TableB,TableA
    Create table TableA (Serial_A int, Amount float)
    Go
    
    INSERT INTO TableA (Serial_A ,Amount) VALUES('1','10')
    INSERT INTO TableA (Serial_A ,Amount) VALUES('1','10')
    INSERT INTO TableA (Serial_A ,Amount) VALUES('2','10')
    INSERT INTO TableA (Serial_A ,Amount) VALUES('2','10')
    INSERT INTO TableA (Serial_A ,Amount) VALUES('3','10')
    Go
    
    
    Create table TableB (Serial_B int, Amount float)
    Go
    
    INSERT INTO TableB (Serial_B ,Amount) VALUES('1','20')
    INSERT INTO TableB (Serial_B ,Amount) VALUES('1','15')
    INSERT INTO TableB (Serial_B ,Amount) VALUES('2','5')
    INSERT INTO TableB (Serial_B ,Amount) VALUES('3','10')
    INSERT INTO TableB (Serial_B ,Amount) VALUES('4','10')
    Go
    
    
    
    Create table #temp
    (
    Serial int, Amount float
    )
    
    
    begin
    DECLARE @Sql_A NVARCHAR(2000)
    DECLARE @Sql_B NVARCHAR(2000)
    
    
    
    		
    set @Sql_A  = 'SELECT [SERIAL], ''AMOUNT'' = SUM(AMOUNT)
    FROM (SELECT ''SERIAL'' = SERIAL_A, ''AMOUNT'' = AMOUNT 
    FROM [dbo].[TableA] 
    UNION ALL
    SELECT ''SERIAL'' = SERIAL_B, ''AMOUNT'' = AMOUNT 
    FROM [dbo].[TableB] )AS A GROUP BY [SERIAL] 
    '
    
    
    Insert into #temp
    Exec(@sql_A)
    
    end
    
    SELECT * FROM #temp
    drop table #temp

    • Marked as answer by xXShanXx Tuesday, April 01, 2014 11:55 AM
    Thursday, March 27, 2014 5:15 PM

All replies

  • Why do you need a dynamic SQL? Can you share something more? Thanks.
    Thursday, March 27, 2014 4:50 PM
    Moderator
  • I need to pass the table name dynamically  and  need to scan more tables too.

    For now I need to to union the result set.

    Thursday, March 27, 2014 4:56 PM
  • I would recommend you to have a quick read  from Erland's excellent review on dynamic sql:

    http://www.sommarskog.se/dynamic_sql.html

    Thursday, March 27, 2014 5:15 PM
    Moderator
  • You need to append to a variable and execute the dynamic sql as below:

    Drop table TableB,TableA
    Create table TableA (Serial_A int, Amount float)
    Go
    
    INSERT INTO TableA (Serial_A ,Amount) VALUES('1','10')
    INSERT INTO TableA (Serial_A ,Amount) VALUES('1','10')
    INSERT INTO TableA (Serial_A ,Amount) VALUES('2','10')
    INSERT INTO TableA (Serial_A ,Amount) VALUES('2','10')
    INSERT INTO TableA (Serial_A ,Amount) VALUES('3','10')
    Go
    
    
    Create table TableB (Serial_B int, Amount float)
    Go
    
    INSERT INTO TableB (Serial_B ,Amount) VALUES('1','20')
    INSERT INTO TableB (Serial_B ,Amount) VALUES('1','15')
    INSERT INTO TableB (Serial_B ,Amount) VALUES('2','5')
    INSERT INTO TableB (Serial_B ,Amount) VALUES('3','10')
    INSERT INTO TableB (Serial_B ,Amount) VALUES('4','10')
    Go
    
    
    
    Create table #temp
    (
    Serial int, Amount float
    )
    
    
    begin
    DECLARE @Sql_A NVARCHAR(2000)
    DECLARE @Sql_B NVARCHAR(2000)
    
    
    
    		
    set @Sql_A  = 'SELECT [SERIAL], ''AMOUNT'' = SUM(AMOUNT)
    FROM (SELECT ''SERIAL'' = SERIAL_A, ''AMOUNT'' = AMOUNT 
    FROM [dbo].[TableA] 
    UNION ALL
    SELECT ''SERIAL'' = SERIAL_B, ''AMOUNT'' = AMOUNT 
    FROM [dbo].[TableB] )AS A GROUP BY [SERIAL] 
    '
    
    
    Insert into #temp
    Exec(@sql_A)
    
    end
    
    SELECT * FROM #temp
    drop table #temp

    • Marked as answer by xXShanXx Tuesday, April 01, 2014 11:55 AM
    Thursday, March 27, 2014 5:15 PM