t-sql to connect to an api like adp api with oauth rest and insert the response in a table

ERPISE 1 Reputation point
2020-12-10T16:34:59.253+00:00

Is it possible to use t-sql to connect to an api like adp api with oauth rest and insert the response in a table? And how do you do that?

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 100.8K Reputation points MVP
    2020-12-10T22:47:53.93+00:00

    You would that with a CLR procedure, that is a procedure written in C#. You can also use COM through sp_OAcreate & co, but that's horrible. CLR is the best option.

    0 comments No comments

  2. MelissaMa-MSFT 24,176 Reputation points
    2020-12-11T07:31:35.413+00:00

    Hi @TomHines-8657,

    As agreed with Erland, you could try with CLR Procedure.

    By using CLR, you could run and manage the code inside the SQL Server. Code that runs within CLR is referred to as a managed code.

    You could create the stored procedures, triggers, user defined types and user-defined aggregates in the managed code. You could achieve significant performance increases because the managed code compiles to the native code prior to the execution.

    You could refer Calling Web Service From SQL Server Using SQL CLR for more details about the steps one by one.

    In addition, if SSIS is permitted, you could also refer below links:
    HTTP POST in SSIS – Send data to Web API url
    SSIS post data to web service API

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Hot issues November--What can I do if my transaction log is full?
    Hot issues November--How to convert Profiler trace into a SQL Server table

    0 comments No comments