Insert into a table from dynamic sql

I have had many situations to put the dynamic sql data to a temp table but first thought was to pass the temp table as parameter or create temp table on dynamic sql itself. But none of these methods would work on SQL. When we create a temp table on dynamic sql, it will not be available outside of dynamic sql scope.


Try this out

DECLARE @tablename VARCHAR(10)
SET @tablename = 'tk'
DECLARE @strQuery NVARCHAR(1000)

SET @strQuery = 'select name from sys.tables where name like ''' + @tablename + '%'''
DECLARE @tablenames TABLE (tblname VARCHAR(100))


INSERT INTO @tablenames
EXEC
sp_executesql @strQuery

SELECT * FROM @tablenames

Easy method to dump all your data from dynamic sql!