Sp_executesql

Overall, I would like to execute dynamic SQL in a temp table and put the result into another temp table.

I have the following in a temp table. It is basically a built-out string of SQL stored in a field.

Field1
SELECT COUNT(*) FROM Table1
SELECT COUNT(*) FROM Table2
SELECT COUNT(*) FROM Table3
SELECT COUNT(*) FROM Table4
SELECT COUNT(*) FROM Table5

I would like to loop through this temp table on Field1; execute the SQL string for each record; get the results and stick it into another temp table.

The final result would be the above table with another field called Record_Count.

I kind of have an idea on how to approach this but I am posting this to see if there are any other cool ways of doing it. For a start, I probably would start with a cursor loop against the first temp table and then use something like "EXECUTE sp_executesql". Am I on the right track?

I wouldn't use a cursor, just construct the SQL into a single string and then execute it. To do it exactly as you requested, would be something like:

Drop Table If Exists #SourceTable
Create Table #SourceTable
(
	Field1 VarChar(100)
)

Drop Table If Exists #Results
Create Table #Results
(
	RecordCount int
)

Insert Into #SourceTable Values
('SELECT COUNT(*) FROM Table1'),
('SELECT COUNT(*) FROM Table2'),
('SELECT COUNT(*) FROM Table3'),
('SELECT COUNT(*) FROM Table4'),
('SELECT COUNT(*) FROM Table5')

Declare @SQL NVarChar(Max) = Null
Select 
	@SQL = IsNull(@SQL + ' Union All ' + Field1, 'Insert Into #Results(RecordCount) ' + Field1)
From #SourceTable

Exec sp_executesql @sql 

    Select * From #Results

If the goal is to build up a list of tables and how many records are in them, however, I'd put far less SQL in the table - just the table name on it's own in fact - because then it simplifies the process of identifying which row in the results belongs to what. For example:

Drop Table If Exists #SourceTable
Create Table #SourceTable
(
	Field1 VarChar(100)
)

Drop Table If Exists #Results
Create Table #Results
(
	TableName VarChar(100),
	RecordCount int
)

Insert Into #SourceTable Values
('Table1'),
('Table2'),
('Table3'),
('Table4'),
('Table5')

Declare @SQL NVarChar(Max) = Null
Select 
	@SQL = IsNull(@SQL + ' Union All SELECT ''' + Field1 + ''', COUNT(*) FROM ' + Field1, 
		'Insert Into #Results(TableName, RecordCount)  SELECT ''' + Field1 + ''', COUNT(*) FROM ' + Field1)
From #SourceTable
print @sql

Exec sp_executesql @SQL

Select * From #Results
1 Like

Andy, that is pretty neat how you wrote that rollup concatenation trick. I knew someone would have approached my question with better results. Thanks!

Andy Hmmm... I am trying to mark your post as in resolving this thread (my question) but not sure why it is not on your post.

There's a much simpler and vastly more efficient way to get row counts:

SELECT table_name, SUM(rows) AS row_count
FROM sys.partitions p
CROSS APPLY (
    SELECT OBJECT_NAME(p.object_id) AS table_name
) ca1
WHERE p.index_id IN (0, 1) 
    --AND table_name IN (...)
GROUP BY table_name
ORDER BY 1
1 Like

Scott, that is interesting.

Instead of passing in Table names, would it be possible to pass in something like this:

SELECT * FROM Table1 T1 INNER JOIN Table2 T2 ON T2.ID = T1.ID
SELECT * FROM Table3 T3 INNER JOIN Table4 T4 ON T4.ID = T3.ID

In other words, it will return the row count for each of the above SELECT?

Ooh, no, sorry. To count the results of a join, you would need to run a query.

Regardless, thank you Scott.

I am almost done by using the "concatenation rollup into a variable" trick that Andy taught me yesterday to do it.

Here is the code. Andy, I made changes to your code for my own learning purpose.

Feel free to critique it Scott and Andy. And again, thank you to both!

--INSERT Table names into table
IF OBJECT_ID('tempdb..#tempTable_Source') IS NOT NULL
BEGIN
	DROP TABLE #tempTable_Source
END

Create Table #tempTable_Source
(
	Field1			VARCHAR(100)
)

INSERT INTO #tempTable_Source VALUES
	  ('Table1')
	, ('Table2')
	, ('Table3')		--This one should have zero record count
	, ('Table4')
	, ('Table5')
---------------------------------------------------------------------
IF OBJECT_ID('tempdb..#tempTable_Results') IS NOT NULL
BEGIN
	DROP TABLE #tempTable_Results
END

Create Table #tempTable_Results
(
	  Table_Name			VARCHAR(100)
	, Record_Count			INT
)
---------------------------------------------------------------------
DECLARE @SQL_Main			NVARCHAR(MAX) = NULL
DECLARE @SQL_Buffer			NVARCHAR(MAX) 

SELECT
--PURPOSE: Build out the SELECT statement
	  @SQL_Buffer = 'SELECT ''' + Field1 + ''' AS Table_Name, COUNT(*) AS Record_Count FROM ' + Field1 + CHAR(13) + CHAR(10)

--NOTICE: The first pass is a NULL so it will stick the INSERT part into the @SQL_Main.
--NOTICE: Then as it goes through each record, it continues the concatenation with the UNION ALL part.
	, @SQL_Main = ISNULL(@SQL_Main + 
						'UNION All' + CHAR(13) + CHAR(10) +	@SQL_Buffer,
						'INSERT INTO #tempTable_Results(Table_Name, Record_Count)' + CHAR(13) + CHAR(10) +	@SQL_Buffer
				)
FROM #tempTable_Source
---------------------------------------------------------------------
PRINT @SQL_Main
EXEC sp_ExecuteSQL @SQL_Main

SELECT
	  *
	--, @SQL_Buffer = 'SELECT * FROM ' + Table_Name			--Can't do this here because will error with this message: A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
	, ISNULL(CASE WHEN Record_Count = 0 THEN '--' END,		'')		 + 'SELECT * FROM ' + Table_Name			AS Dynamic_SQL
FROM #tempTable_Results

Using sys.partitions to get rowcount is ok if you can cope with the fact it's a very approximate value. Mostly it's ok, particularly with clustered indexes, but can be wildly inaccurate at other times.

Your re-written version looks ok to me and probably better commented than most of my code!