#PowershellBasics: Bulk insert.

Leave a comment

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,579 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: