Updating SQL table from Excel sheet (using powershell)

Question

I am using the below code to complete a bulk import into SQL, but I think I have run into a problem with my method.

$serverName = "pjim-app-02\tsg";
$databaseName = "User_Data" ;
$tableName = "dbo.AD$" ;
$filepath = "\\pjim.bne.catholic.edu.au\emmauscollege\media\cardexchange\carddata.xlsx";

#create object to open Excel workbook
$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open($filepath)
$Worksheet = $Workbook.Worksheets.Item(1)
$startRow = 2

#create System.DataTable
    $dt = new-object "System.Data.DataTable"
    [void]$dt.Columns.Add("StaffStudentID", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("SurName", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("FirstName", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("Year", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("HomeGroup", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("DOB", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("StaffStudent", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("email", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("ImageName", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("CardKey", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("F11", [System.Type]::GetType("System.String"))
    Do {
        $ColValues1 = $Worksheet.Cells.Item($startRow, 2).Value()
        $ColValues2 = $Worksheet.Cells.Item($startRow, 3).Value()
        $ColValues3 = $Worksheet.Cells.Item($startRow, 4).Value()
        $ColValues4 = $Worksheet.Cells.Item($startRow, 5).Value()
        $ColValues5 = $Worksheet.Cells.Item($startRow, 6).Value()
        $ColValues6 = $Worksheet.Cells.Item($startRow, 7).Value()
        $ColValues7 = $Worksheet.Cells.Item($startRow, 8).Value()
        $ColValues8 = $Worksheet.Cells.Item($startRow, 9).Value()
        $ColValues9 = $Worksheet.Cells.Item($startRow, 10).Value()
        $ColValues10 = $Worksheet.Cells.Item($startRow, 11).Value()
        $ColValues11 = $Worksheet.Cells.Item($startRow, 12).Value()
        $startRow++
        $dt.Rows.Add($ColValues1,$ColValues2,$ColValues3,$ColValues4,$ColValues5,$ColValues6,$ColValues7,$ColValues8,$ColValues9,$ColValues10,$ColValues11)
        }
    While ($Worksheet.Cells.Item($startRow,2).Value() -ne $null)

$Excel.Quit()

#connect to SQL Server and import the system.data.table
$SQLServerConnection = "Data Source=$serverName;Integrated Security=true;Initial Catalog=$databaseName;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $SQLServerConnection
$bulkCopy.DestinationTableName = $tableName
$bulkCopy.WriteToServer($dt)

This will only import everything, not just the changes. I thought I would be able to get around this by enforcing a unique key, so that it would not import the records that already exist, but instead it just errors out during its bulkcopy stage.

Can someone please advise if there is a way using the above code that can complete this as an update?

As a work around I was going to import it into a new table, sync the new table with the existing one inside of SQL, then wipe the data from the new table ready for the next time the script runs.

This is for a rather simple recording database, and will only need to be run once a day.

Can anyone advise if I can re-use this script (as its already partly in place), or if there is a better way to achieve this (I have been unable to get the excel sheet setup as a linked table due to permissions)?

Thank you, Wofen

Answer 1

Bulk load processes do just that - load data. They are not intended for updating existing data, or even for adding rows that don't already exist. As you surmised, the best method for those things is generally to load the data to a staging table, run a process that moves staging data to the main table (whether that's updating or replacing existing rows adding new rows, or some combination of the above), then clearing the staging table.

I'd set up a stored procedure in SQL to handle the data in the staging table, and run that after loading the staging table. You could use your existing script, but would need to add the code to run the stored procedure (and, of course, would need to create the stored procedure on your SQL Server).