September 21, 2021 by Kenneth Fisher
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.