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
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
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!