sql-serverpowershellcsv

Import multiple rows from CSV to SQL Server and create a new ID for each one


I'm trying to extract several "agent" rows from a .CSV file and send them to SQL Server, but the .CSV file Num Contrat.csv may be the same for several agents, whereas it must be unique in SQL Server.

Example: there is an agent from my file:

enter image description here

Fields are Matricule / Name / DateDebut (starting Date) / DateFin (Ending Date) / ETP / Num Contrat

The last field is the most important, because it's the most problematic. It is identical in the .CSV, but must be unique in SQL Server.

Here's the logic I'd like to use in my code:

enter image description here

And here's how I try to get this result in my script:

try {
# Data
$matriculeContrat = $ligne.MATRICULE
$numContratOriginal = $ligne.'Num Contrat'
$abrTypContrat = $ligne.'Abr Typ Contrat'
$idEtablissement = [string]$ligne.'Num Dossier'

# Delete two characters and increment ContractNumber
$baseNumContrat = $numContratOriginal.Substring(0, [Math]::Max(0, $numContratOriginal.Length - 2))
$newNumContrat = $numContratOriginal
$suffix = 1

# Generate new contract number - Max 15 chars (doesn't work at all ?)
     while ((Invoke-Sqlcmd -Query "SELECT COUNT(1) FROM Contrats WHERE [Num Contrat] = N'$newNumContrat' AND Matricule = N'$matriculeContrat'" -ServerInstance $serverInstance -Database $database)[0].Column1 -gt 0) 
     {
         $newNumContrat = $baseNumContrat + ($suffix.ToString("D2"))
         if ($newNumContrat.Length > 15) 
         {
              $newNumContrat = $newNumContrat.Substring(0, 15)
         }
         $suffix++
     }

$insertContratQuery = @"INSERT INTO Contrats ([Num Contrat], Avenant, [Date début], [Date Fin], Type, [Temps Base], IDRégime, [Régime Mod], [Base contractuelle], RTT, [travail de nuit], [Règle équivalence], MultiEtablissement, [Matricule agent remplace], [Motif remplacement], [Convention collective], [Num contrat base], IDEtablissement, Matricule, [Report CPA], [Report Ancienneté], [Report CESS], Commentaire) 
VALUES (N'$newNumContrat', N'CONTRAT', $debutContratSql, $finContratSql, N'$abrTypContrat', $etp, N'MOD', NULL, $baseContractuelle, 0, $travailNuit, 0, 0, NULL, NULL, N'CCNT 66', N'$numContratOriginal', N'$idEtablissement', N'$matriculeContrat', N'0.00', 0, 0, NULL)"

@Invoke-Sqlcmd -Query $insertContratQuery -ServerInstance $serverInstance -Database $database
Write-Host "Contrat ajouté pour le matricule : $matriculeContrat avec Num Contrat : $newNumContrat"

    } 
catch 
    {
    $errorMessage = "Erreur lors de l'ajout du contrat pour le matricule $matriculeContrat : $($_.Exception.Message)"
    Write-Host "Requête SQL problématique pour le matricule $matriculeContrat : $insertContratQuery"
    Add-Content -Path $sqlDebugFilePath -Value "Requête SQL pour le matricule $matriculeContrat : $insertContratQuery`nErreur : $errorMessage`n"
    }

The insert goes well for the first contracts (so I have 1 copy of each agent, with their "unique" original contract number without any suffix, however in Powershell I get this error several times:

Invoke-Sqlcmd : Violation de la contrainte PRIMARY KEY 
«PK_Contrats». Impossible d'insérer une clé en double dans
l'objet «dbo.Contrats». Valeur de clé dupliquée: (73065110180903).
L'instruction a été arrêtée.
Au caractère C:\Interconsult\Script_GetMS_Update.ps1:192 : 5
+     Invoke-Sqlcmd -Query $insertContratQuery -ServerInstance 
$serverI ...
+     
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation : (:) [Invoke-Sqlcmd], 
SqlPowerShellSqlExecutionException
+ FullyQualifiedErrorId : 


SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Simply a duplicate primary key (Num Contract) error, so my incrementing doesn't work.

I've tried several things and I can't figure out how to solve my problem, if it works at all.

I hope everything is clear, thank you for your help.


Solution

  • NEW SOLUTION, updated based on comments:

    use unused columns in the Database to store the Original Contract Number and pur a unique ID in the "Num Contrat" column.
    Comment the code in abundance to explain the shenaningan.

    OLD SOLUTION, not appliable to the live situation:

    To be clear: you want to *replace* the last two characters in the "Num Contract" field? At this point, just add a progressive while importing.
    $Counter = 0 
    $UniqueContractArray = $CsvPath | Import-Csv | 
        Select-Object -Property 'matricule', 'Name', 'DateDebut', 'DateFin', 'ETP', 
            @{ 
                Name = 'Num Contract'
                Expression = { '{0}{1:d4}' -f $_.'Num Contract', $script:counter++ } 
            } 
    

    This way you mantain the original contract number(potentially useful) while also know the last N digits are to differentiate them same-numbered contracts.
    Replace the 4 in {1:d4} with how many digits you want\need or add more logic if you want to reset after some number(depending on how many same-numbered contracts you can expect, I guess)