INSERT values to a SQL Server table

A recent posting on the PowerShell newsgroup asked, among other things, how easy it is to write data into a SQL Server table.  The question was in relation to storing free disk space from all the servers in the network

 

I realised that was something I hadn’t looked at forPowerShell.  OK – I know how to do it for C# – dig out the ADO book, check the syntax and off we go.  First fire up the VM with SQL Server in it.  Create a test database with Table1 consisting of 3 columns, server, drive and freespace.  Then we run the following:

 

Clear-Host

## open database connection

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=SQL1; Initial Catalog=Test; Integrated Security=SSPI")
$conn.Open()

$cmd = $conn.CreateCommand()

$cmd.CommandText ="INSERT Table1 VALUES (‘Server1’, ‘C:’, 456)"
$cmd.ExecuteNonQuery()
$conn.Close()

 

Create a connection object where data source = the server and Initial Catalog = the table.  Integrated Security implies using Windows Authentication.  From the connection we create a sqlcommand and use the CommandText property to define our query.  Note the double quotes round the whole string and single quotes round the individual values.  It doesn’t work the other way round.  We then use the ExecuteNonQuery() method which is used when we don’t expect any rows to be returned.  Finally close the connection. 

 

This could easily be parameterised or even turned into a function.  Similar code could be used for performing an UPDATE as well.