Powershell - Ingesting API json results into SQL Server table

Looking for some help with ingesting data retrieved from an API endpoint and inserting it into SQL Server. This endpoint returns json.

I'm trimming the script to make this more readable. The script gets the data I'm requesting, the issues is the insert into the SQL table.

# Pass the `Authorization` header value with the payload we calculate from the id + secret
$Time = Invoke-RestMethod -Uri $TimeCardURL -Method Get -Header @{ Authorization = "Bearer ${AuthorizationValue}" } -Body $Body -Certificate $Cert -ContentType 'application/json'


$Time.teamTimeCards | Select-Object associateOID,timeCards

#SQL authentication and insert $Result data 
$serverName = "sql-server"
$databaseName = "database"
$tableName = "table"
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$serverName';database='$databaseName';trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
foreach($ID in $associateOID){
$insertquery="
  INSERT INTO $tableName
      ([associateOID],[timeCards])
    VALUES
      ('$associateOID','$timeCards')"
  $Command.CommandText = $insertquery
  $Command.ExecuteNonQuery()
}
$Connection.Close();

It's trying to load the entire object $associateOID into the column, which is resulting in a "String or binary data would be truncated in table 'database.table', column 'associateOID'. Truncated value: '1 2 3 4 5 6 7 8'. The statement has been terminated."

I'm guessing there is an issue with this section of the script

$Time.teamTimeCards | Select-Object associateOID,timeCards

EDIT: The issue was I needed to declare the values inside the foreach statement

This is what I had

foreach($ID in $associateOID){
$insertquery="
  INSERT INTO $tableName
      ([associateOID],[timeCards])
    VALUES
      ('$associateOID','$timeCards')"
  $Command.CommandText = $insertquery
  $Command.ExecuteNonQuery()
}

This is what fixed it with a little more massaging. Thanks siggemannen for your help

foreach($record in $Time.teamTimeCards){
  $aOID = $record.associateOID
  $timeCards = $record.timeCards | ConvertTo-Json -Compress -Depth 100
$insertquery="
  INSERT INTO $tableName
      ([associateOID], [timeCards])
    VALUES
      ('$aoid', '$timeCards')"
  $Command.CommandText = $insertquery
  $Command.ExecuteNonQuery()
}

I rewrote your code to use another source of data, but you probably get the idea:

$output = Get-Process | Select-Object Handles, Id, ProcessName
echo($output)

foreach($value in $output){
$id = $value.Id
$name = $value.ProcessName
$insertquery="
  INSERT INTO $tableName
      ([ID],[Name])
    VALUES
      ('$id','$name')"
  echo($insertquery)
  
}