Powershell insert into sql table

28 March,2017 by Jack Vamvas

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

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Powershell insert into sql table


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer