# Python and Dynamic SQL

**URL:** <https://forum.freecodecamp.org/t/python-and-dynamic-sql/592204>\
**Category:** Curriculum Help\
**Tags:** python\
**Created:** [February 14, 2023, 9:57pm UTC](https://forum.freecodecamp.org/t/python-and-dynamic-sql/592204 "2023-02-14T21:57:52Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![ns309](https://avatars.discourse-cdn.com/v4/letter/n/91b2a8/32.png) [@ns309](https://forum.freecodecamp.org/u/ns309)\
**Post date:** [February 14, 2023, 9:57pm UTC](https://forum.freecodecamp.org/t/python-and-dynamic-sql/592204/1 "2023-02-14T21:57:52Z")

</div>

I am very new to python.

I have created a stored procedure in SSMS where I loop through the tables names and run select statements on each of those tables and insert the results of those queries into a table.

I would like to achieve the same result using python.

Fo eaxmple I have a table in ssms with tablename and schema name  
tablename - employees, sales, fruits, admin, products  
schemaname - dbo.test2023, dbo.test2023, dbo.test2023,dbo.test2023,dbo.test2023

I would like to create a python script where I connect to SSMS and loop through the above tablename and run the below sql statements on each of the table  
select count(id) from tablename;  
select count(id) from tablename where apple = 1;  
select count(id) from tablename where apple = 0;

And insert the results of the above queries into another table

I don’t know if this can be achieved in python.

Please can anybody help me with the code for the above.

Many thanks for your help.

---

<div class="post-metadata">

**Author:** ![ns309](https://avatars.discourse-cdn.com/v4/letter/n/91b2a8/32.png) [@ns309](https://forum.freecodecamp.org/u/ns309)\
**Post date:** [February 15, 2023, 11:37am UTC](https://forum.freecodecamp.org/t/python-and-dynamic-sql/592204/3 "2023-02-15T11:37:19Z")

</div>

Yes, all the tables are in the same database. I have got the stored procedure working. But I want to learn python and was hoping that there is way to do this in python.

---

<div class="post-metadata">

**Author:** ![ns309](https://avatars.discourse-cdn.com/v4/letter/n/91b2a8/32.png) [@ns309](https://forum.freecodecamp.org/u/ns309)\
**Post date:** [February 16, 2023, 12:48pm UTC](https://forum.freecodecamp.org/t/python-and-dynamic-sql/592204/5 "2023-02-16T12:48:33Z")

</div>

I am not able to post the code as it keep getting error message only two users can be mentioned!

---

<div class="post-metadata">

**Author:** ![ns309](https://avatars.discourse-cdn.com/v4/letter/n/91b2a8/32.png) [@ns309](https://forum.freecodecamp.org/u/ns309)\
**Post date:** [February 16, 2023, 12:49pm UTC](https://forum.freecodecamp.org/t/python-and-dynamic-sql/592204/6 "2023-02-16T12:49:05Z")

</div>

An error occurred: Sorry, new users can only mention 2 users in a post.

---

<div class="post-metadata">

**Author:** ![ns309](https://avatars.discourse-cdn.com/v4/letter/n/91b2a8/32.png) [@ns309](https://forum.freecodecamp.org/u/ns309)\
**Post date:** [February 16, 2023, 1:04pm UTC](https://forum.freecodecamp.org/t/python-and-dynamic-sql/592204/7 "2023-02-16T13:04:55Z")

</div>

```auto
USE [test_dbepictype_WIP]
GO

SET ANSI_NULLS ON
GO

IF OBJECT_ID ( 'sp_CheckMissingRows', 'P' ) IS NOT NULL   
    DROP PROCEDURE [dbo].[sp_CheckMissingRows];  
GO

-- Add the parameters for the stored procedure here
CREATE PROCEDURE [dbo].[sp_CheckMissingRows]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--decalre scalar variables
‘’’
DECLARE @I INT,
            @RowCount INT,
            @SQL VARCHAR(MAX),
            @Table VARCHAR(100)
--declare table variable to store list of tables to loop through
        DECLARE @T TABLE
            (
              [ID] [int] IDENTITY(1, 1),
              [TableName] [varchar](100),
 [SchemaName] [Varchar](100)
            )

        INSERT INTO @T ( TableName, SchemaName )
                SELECT TableName, SchemaName
                FROM [test_10012023].[dbo].[tbl_reference1]
      
 --count how many rows in the table variable
         SET @RowCount = ( SELECT COUNT(*) FROM @T)   
         SET @I = 1

--loop though the rows in the table variable 
--and build a sql statement using the table details for each row
WHILE ( @I <= @RowCount )
            BEGIN
--fetch the table name and schema name for the current row into variables
SET @Table = ( SELECT SchemaName+'.'+TableName
  FROM @T
  WHERE ID = @i
 )

PRINT 'Starting ' + @Table

set @sql = 'Insert into [test_10012023].[dbo].[tbl_results] (Tablename,Variable_name, Result) SELECT ''' + @Table +''' as Tablename, ''Count_of_id''as variable_name, count(distinct(id)) as Result FROM ' + @Table +';
Insert into [test_10012023].[dbo].[tbl_results] (Tablename,Variable_name, Result) select ''' + @Table +''' as Tablename, ''Count_of_id_realrow1'' as variable_name,count(id) as Result FROM ' + @Table +' where RealRow = 1;
Insert into [test_10012023].[dbo].[tbl_results] (Tablename,Variable_name, Result) select ''' + @Table +''' as Tablename, ''Count_of_id_realrow0'' as variable_name,count(serno) as Result FROM ' + @Table +' where RealRow = 0;
Insert into [test_10012023].[dbo].[tbl_results] (Tablename,Variable_name, Result) select ''' + @Table +''' as Tablename,''duplicate_id'' as variable_name, id as Result FROM ' + @Table +' WHERE NOT EXISTS 
(
SELECT [id] FROM [test_10012023].[dbo].[tbltable1]
);
Insert into [test_10012023].[dbo].[tbl_results] (Tablename,Variable_name, Result) select ''' + @Table +''' as Tablename, ''Count_of_id_missing'' as Variable_name, count(A.id) as Result FROM ' + @Table +' A
LEFT JOIN [test_10012023].[dbo].[tbltable1] B
ON A.id = B.id
WHERE B.id IS NULL;
Insert into [test_10012023].[dbo].[tbl_results] (Tablename,Variable_name, Result) select ''' + @Table +''' as Tablename, ''Count_of_id_missing_realrow0''as Variable_name,count(A.id) as Result FROM ' + @Table +' A
LEFT JOIN [test_10012023].[dbo].[tbltable1] B
ON A.id = B.id
where B.id is not null
and A.RealRow = 0;'
print @SQL
EXEC (@SQL)
SET @I = @I+1
‘’’
END
END
GO
EXEC [dbo].[sp_CheckMissingRows] 
GO

```

---

<div class="post-metadata">

**Author:** ![system](https://global.discourse-cdn.com/freecodecamp/original/3X/e/1/e1c433102e34b46dfaa3e15d13ab9a514b127078.svg) [@system](https://forum.freecodecamp.org/u/system)\
**Post date:** [August 18, 2023, 1:04am UTC](https://forum.freecodecamp.org/t/python-and-dynamic-sql/592204/8 "2023-08-18T01:04:57Z")

</div>

Closed on August 18, 2023, 1:04am UTC

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.