In fact, your needs are essentially the same as the functionality of the Business Connectivity Services.
SharePoint has a type of List called the External List which is used for displaying data that comes from Business Connectivity Services (BCS). You can connect with any external source (in your case it is a SQL database table) and can create a list from that source.
Based on your description, you seem to have a general understanding of this feature. What problems did you encounter with the actual deployment?
You can refer to the following article for a more specific understanding of BCS:
Get started with Business Connectivity Services in SharePoint
You also need to understand how to create external content type for SQL Server:
Create external content types for SQL Server in SharePoint
Finally, you can refer to the following article, which uses SharePoint designer. This may be more convenient for your operation.
SharePoint List From SQL Server Table
SharePoint Business Connectivity Service – Create External Content Type and External List
Please note: Microsoft provides third-party contact information to help you find technical support. This contact information may change without notice. Microsoft does not guarantee the accuracy of this third-party contact information.
If the response is helpful, please click "Accept Answer" and upvote it. 😊