June 8, 2018 at 2:48 am
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
June 8, 2018 at 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
June 8, 2018 at 4:44 am
John Mitchell-245523 - Friday, June 8, 2018 4:06 AMIf 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_idJohn
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