2

I have this code that bulk copies into a SQL Server table from $dmvResult(data table).

$dmvResult = DMV_Query 'SELECT [SESSION_ID], [SESSION_SPID]
                        FROM $SYSTEM.DISCOVER_SESSIONS';
$ConnectionString ='Data Source={0}; Database={1}; Trusted_Connection=True;' -f $Server,$DB

$bulkCopy = new-object Data.SqlClient.SqlBulkCopy($ConnectionString)
$bulkCopy.DestinationTableName=$TableSomething

foreach ($column in $dmvResult.Columns) { 
     $bulkCopy.ColumnMappings.Add($column.ColumnName, $column.ColumnName) > $null 
}

$bulkCopy.WriteToServer($dmvResult)

It works flawlessly, however, it appends the data so more and more rows are created. Can I just somehow overwrite the data in the SQL Server table every time I run the script? I don't want to retain the existing data, but instead overwrite each time the script runs

Essentially, I want to do something like this: 

$bulkCopy.WriteToServer($dmvResult) | overwrite
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Cataster
  • 3,081
  • 5
  • 32
  • 79
  • Isn't that the same as this: [How to automatically map columns from DataTable to a SQL Table with BulkCopy?](https://social.technet.microsoft.com/Forums/en-US/25e54426-e4f2-40d2-907e-847f09ffd6f3/how-to-automatically-map-columns-from-datatable-to-a-sql-table-with-bulkcopy?forum=winserverpowershell) ? – Olaf Feb 01 '19 at 01:23
  • 1
    @Olaf yes, however, that was specifically about how to map. now I resolved that issue, so my new question is overwriting, which is why i opened a new thread as i have been advised to do in the past for any new topics – Cataster Feb 01 '19 at 01:24
  • Delete the data out of the table first – Nick.Mc Feb 01 '19 at 04:43
  • @Nick.McDermaid darn, I was hoping for an overwrite method or property of bulk copy – Cataster Feb 01 '19 at 05:49
  • I just checked the definition of bulkcopy and there doesn’t appear to be one. Be aware that if the source and target tables are on the same server, this would be much more efficient to run a single query rather than moving data out and in to the database. – Nick.Mc Feb 01 '19 at 05:55

1 Answers1

-1

I have the same question. The only work around I see is to create a staging table and bulkCopy to that. Then use a stored procedure to merge the staging table and the table you'd like to overwrite.

gilbertbw
  • 634
  • 2
  • 9
  • 27
Toucan Jam
  • 61
  • 1
  • 4
  • we actually ended up needing all data, so i didnt have to overwrite anymore. but one way to do it if you still wanna overwrite is just truncate the table over and over, even based on a timestamp. – Cataster Jan 23 '20 at 18:07