UNION table from a cursor result SQL Server

sandi 0
Viewed 28

I am trying to insert my cursor result into table. Can anyone help?

SELECT DISTINCT
    [Source System], [Business Transaction ID], [Contract Layer ID], Comment 
INTO
    #temp
FROM 
    dbo.FPSL_OverseasExceptions 
ORDER BY
    1, 2, 3

DECLARE commands CURSOR FOR
    SELECT DISTINCT 
        txt = 'SELECT TOP 10 * FROM #temp WHERE Comment = ''' + Comment + '''  ' 
    FROM #temp

DECLARE @cmd Varchar(MAX)

OPEN commands

FETCH NEXT FROM commands INTO @cmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@cmd) 

    FETCH NEXT FROM commands INTO @cmd
END

CLOSE commands
DEALLOCATE commands

From this code I got several table result, I want to union them in one temp table so I can manipulate further. Can anyone help me?

cursor insert union sql-server sql
1 Answers
    DROP table if exists #Target
CREATE TABLE #Target  (
      [Source System] nvarchar(255), [Business Transaction ID] nvarchar(255), [Contract Layer ID] nvarchar(255), Comment nvarchar(255))

DROP table if exists #temp

SELECT DISTINCT
     [Source System], [Business Transaction ID], [Contract Layer ID], Comment 
INTO
    #temp
FROM 
    dbo.FPSL_OverseasExceptions 
ORDER BY
    1, 2, 3

DECLARE @cmd NVarchar(MAX) = '';

DECLARE commands CURSOR FAST_FORWARD FOR
    SELECT DISTINCT 
        txt = N'SELECT TOP 10 * FROM #temp where  Comment = ''' + Comment + '''  ' 
    FROM #temp
    
OPEN commands

FETCH NEXT FROM commands INTO @cmd

WHILE @@FETCH_STATUS = 0
BEGIN
    
    INSERT #Target
    EXEC(@cmd) 
    FETCH NEXT FROM commands INTO @cmd
END

CLOSE commands
DEALLOCATE commands

select * from #Target
Stas0
Related