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:
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:
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.
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:
$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)