Powershell insert into sql table

Question: Could you supply details on using  Powershell to insert data into a SQL table. I have the data in a Powershell DataTable - but am struggling with how to generate the commands to INSERT data .

I've read SQL Server – Export Excel data to SQL Server with Powershell (SQL ...  which uses the SQLBulkCopy method. But I'm looking to use an INSERT statement.

Answer: As you already have the data in a DataTable - you are just a few steps away from being able to INSERT the data into a sql table.

 A typical scenario could be you've collected some data from some data sources , such as a CSV file , you've create a DataTable ,  do something with the data and then you want to store it into a SQL Server table.

 To view details on how to create a SQL Server connection Database Connectivity test with Powershell

In this code snippet - $dt is a Powershell DataTable - where some rows have been added. Then for every row in the DataTable ($dt ) do something.

In thi case we are connecting to the SQL Server database using the connection ($cn2) - created earlier , and then executing an INSERT statement . We are adding 2 values

$dt | FOREACH-OBJECT {
	$cmd  = new-object System.Data.SQLClient.SQLCommand
	$cmd.CommandText = "INSERT INTO myTable(server,instance) VALUES(@server.@instance)"
	$cmd.Connection = $cn2
	$cmd.Parameters.AddWithValue("@server", $_.server) | Out-Null
        $cmd.Parameters.AddWithValue("@instance", $_.instance) | Out-Null
        $rowsUpdated = $cmd.E xecuteNonQuery()
}

Let me know if any more details are required

Read More

Expand your Powershell mind – Three key cmdlets (SQL Server DBA)

Powershell Scripts for DBA