PS Update SQL table

PowerShell Help
1

I have this little nifty thing that creates a SQL table for me, grabs the some data from AD and throws it into the table. Then next time the script runs, it drops the table, creates new one and re-populates it with data. (yes, yes, I know, I have my username/pass stored here, in my prod I actually use Integrated Security :slight_smile:
$starttime = Get-date
$search_user = “ou=someOU,dc=domain,dc=blah”
$props = “AccountExpirationDate”,“c”,“co”,“countrycode”,“SamAccountName”,“userPrincipalName”,“objectSID”
$data_user = Get-ADUser -SearchBase $search_user -Filter {objectclass -eq ‘user’} -Properties $props -ResultSetSize 900 | Select-Object AccountExpirationDate,c,co,`
SamAccountName,userPrincipalName,objectSID | Sort-Object -Property sn
$DBServer = “someserver\someinstance”
$DBName = “someDB”
$TableName = “someTable”
$Uid=“sa”
$Pwd=“Password123”
$Create = “CREATE TABLE $TableName (objectSID nvarchar (200) not null PRIMARY KEY, SamAccountName varchar (100), UserPrincipalName nvarchar (100), AccountExpirationDate nvarchar (40), CountryCode varchar (2), Country varchar (40))”
$SQLCon = New-Object System.Data.SqlClient.SqlConnection(“Data Source=$DBServer; `
Initial Catalog=$DBName;Integrated Security=False;User ID=$Uid;Password=$Pwd”)
$SQLCon.open()
$SQL = $SQLCon.CreateCommand()
$SQL.CommandText = $Create
$SQL.ExecuteNonQuery() > $null
$SQL.ExecuteNonQuery()
$X = 0
foreach ($User in $data_user)
{
$SQL.CommandText = “INSERT INTO $TableName (objectSID,AccountExpirationDate,CountryCode,Country,SamAccountName,userPrincipalName) `
VALUES (@objectSID,@AccountExpirationDate,@CountryCode,@Country,@SamAccountName,@userPrincipalName)”;
if ($User.UserPrincipalName -eq $null)
{$User.UserPrincipalName = [System.DBNull]::Value};
if ($User.AccountExpirationDate -eq $null)
{$User.AccountExpirationDate = [System.DBNull]::Value};
if ($User.c -eq $null)
{$User.c = [System.DBNull]::Value};
if ($User.co -eq $null)
{$User.co = [System.DBNull]::Value};
$SQL.Parameters.clear();
$SQL.Parameters.Add(“@objectSID” , $User.objectSID.value);
$SQL.Parameters.Add(“@SamAccountName” , $User.SamAccountName);
$SQL.Parameters.Add(“@UserPrincipalName” , $User.UserPrincipalName);
$SQL.Parameters.Add(“@AccountExpirationDate” , $User.AccountExpirationDate);
$SQL.Parameters.Add(“@AccountType” , “User account”);
$SQL.Parameters.Add(“@CountryCode” , $User.c);
$SQL.Parameters.Add(“@Country” , $User.co);
$SQL.ExecuteNonQuery();
$X = $X + 1
}

“$X Records written to $TableName in database $DBName on server $DBServer”
$SQLCon.Close()
$endtime = get-date
Write-host $starttime
Write-host $endtime

All good so far, what I would like to, instead of dropping the table, I’d like to just update it based on the objectSID field, in other words, if $user.objectSID -eq @objectSID update rest of the fields, if object with such value doesn’t exist, add it to the table, at the same time, I want to make sure NULL values don’t cause any issues. I’ve been trying to find something that would point me in the right direction but I can’t get anything to work. Any help is appreciated.
Thanks!

2

I haven’t studied the code in detail, please use the instructions in Bold just above were you type the post.
It will format the code and indentations will be preserved, much easier to read.

But in general I would go about in this way.

  1. Pull the table from SQL into a variable in Powershell (unless it’s a humongous table).
  2. Do e.g. a foreach on the $data_user list and compare each if they are in the variable from step 1.
  3. If object is found update, if not add to table

Regarding step 3 you could either create a sql statement string that you keep adding to, or push the add/update right away.
Kind of depends on what you want to do.
If you build up the “dynamic” SQL statement then you would update the table after the foreach not within the foreach loop.

E.g.

  1. Start sql statement
  2. Loop throught the data and add to the sql statement.
  3. End the sql statement
  4. Update the sql table with the assembled sql statement.