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.