In my last post I talked about invoke-sqlcmd. It’s a nice easy way to run a query or a .sql file. The project I’m working on has me looping through a number of SQL Instances, grabbing some information (in this case who’d logged in recently), and then writing that information out to a central repository. In this post I’m going to talk about how I wrote the data back to my SQL Instance. It’s a bit more complicated than invoke-sqlcmd, but still not overly difficult. And again, standard disclaimer, this isn’t the only way to do this task, it’s just the one I found and chose to use.

#Define output variables
$OutputInstance    = "InstanceName"
$OutputDatabase  = "DatabaseName"
$OutputTable         = "LastLogins"
$OutputConnectionString    = "Data Source="+$OutputInstance+"; Integrated Security=True;Initial Catalog="+$OutputDatabase+";"

#Create the bulk copy object
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $OutputConnectionString 

#Define the destination table 
$bulkCopy.DestinationTableName = $OutputTable
#Bulk copy data
$bulkCopy.WriteToServer($myData)

In the first section above I’m declaring and setting the initial values for my variables. I probably don’t need to split it out quite so much for something this simple, but I found that this way it’s easy for me to change any of the pieces, and, more importantly, easier for other people to understand.

Next I create an instance of the SqlBulkCopy object. There are a fair number of properties and methods available and I recommend glancing at the BOL link but here I’m being very simple. I create the object passing in the connection string, set the destination table property, and then use the WriteToServer method to write the contents of $myData (the recordset I created in the previous post) to the destination table. As with any bulk copy the structures have to match and it’s an append, not an overwrite.

Like I said, pretty easy.