First create the connection object, set the connection string and open the connection:

$objSqlConnection = New-Object System.Data.SqlClient.SqlConnection
$objSqlConnection.ConnectionString = "Server=YourSqlServer; Database=YourDataBase; User Id=YourUsername; password=YourPassword"

You can also use “Integrated Security=true;” instead of “User Id” and “password” to login with the user executing the script.
Now create a SqlCommand, set its query and execute it:

$objSqlCommand = $objSqlConnection.CreateCommand()
$objSqlCommand.CommandText = "INSERT INTO northwind.dbo.testtable (YourColumnName) VALUES (YourColumnData)"

This will execute inserts and updates, commands that will not return any data. To query your tables you can use following code.

Create a reader, set a query again, execute it and read its results:

$objSqlCommand.CommandText = "SELECT column_str, column_int, column_flt,  FROM northwind.dbo.testtable"
$objSqlReader = $objSqlCommand.ExecuteReader()
  $column_str = $objSqlReader.GetString(0)
  $column_int = $objSqlReader.GetInt32(1)
  $column_flt = $objSqlReader.GetFloat(2)

Finally close the sql connection.


Documentation for functions, methods and properties used in this post: