Unable to store result of a dynamic Query in a temporary table

  • Hello Everyone,

    I am facing an issue while inserting records of a dynamic SQL, I am passing table name dynamically to a table .
    I  want to insert the  result of a query into temporary table.

    declare
    table_cnt cursor static for select name from sys.objects
    declare @cnt int, @rcnt int
    declare @tbname varchar(4000), @sql varchar(4000)
    create table #t (column_val varchar(4000), cnt int)
    open table_cnt
    fetch next from table_cnt into @tbname
    while (@@FETCH_STATUS = 0)
        begin
            set @sql= 'select @rcnt = COUNT(*) as rowcnt from '+ @tbname
            EXECUTE ( @sql )
            insert into #t values(@tbname, @rcnt)
            fetch next from table_cnt into @tbname
        end
    print 'Total no.of reords : ' + convert(varchar, @@cursor_rows)
    close table_cnt
    deallocate table_cnt

    How to insert EXECUTE ( @sql ) result into temporary table #t ?

    Can anyone  help me to resolve this issue ?

    Thanks in Advance,
    Prasanna

  • If you want to put results from dynamic SQL into variables, you need to use sp_executesql instead of EXEC.  Or you can take the variables out of the dynamic SQL altogether and so it like this:
    set @sql= 'select ''' + @tbname +''' AS TableName, COUNT(*) as rowcnt from '+ @tbname
    insert into #t
    EXECUTE ( @sql )

    Better still, lose the cursor altogether:
    SELECT
         OBJECT_NAME(object_id) AS TableName
    ,    SUM(rows) AS RowCnt
    FROM sys.partitions
    WHERE index_id in (0,1) -- heap or clustered index
    GROUP BY object_id

    John

  • John Mitchell-245523 - Friday, June 8, 2018 4:06 AM

    If you want to put results from dynamic SQL into variables, you need to use sp_executesql instead of EXEC.  Or you can take the variables out of the dynamic SQL altogether and so it like this:
    set @sql= 'select ''' + @tbname +''' AS TableName, COUNT(*) as rowcnt from '+ @tbname
    insert into #t
    EXECUTE ( @sql )

    Better still, lose the cursor altogether:
    SELECT
         OBJECT_NAME(object_id) AS TableName
    ,    SUM(rows) AS RowCnt
    FROM sys.partitions
    WHERE index_id in (0,1) -- heap or clustered index
    GROUP BY object_id

    John

    Thank you so much .. 🙂 It worked for me ..

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply