How to insert data into a SQL Server Table using Powershell using Invoke-SQLcmd Commandlet

Being a SQL Server DBA , while dealing with reporting and automation stuffs, it is quite needed to insert the data into SQL Server table using shell or batch scripts Here , we are going to use powershell to insert the data using a commandlet in Powershell called Invoke-SQLCmd. There are many other options also to do the same but we are focusing Invoke-SQLcmd to perform this in this session.

What we are doing

Insert the Services running in a windows server to a table in SQL Server.

What we need

SQL Server installed in the machine\Server.

How we are doing

1.       Take the output of the Get-Services command.

2.       Insert the data to SQL Server table ‘ServiceTable’

Steps

-          This is how the Get-Service commandlet output looks like.

Script to extract the details and insert into SQL server table.

Powershell Script

$services=Get-Service
foreach($service in $services)
{
$ser=$service.Status
$name=$service.name
$disname=$service.DisplayName

$insertquery="
INSERT INTO [dbo].[ServiceTable]
           ([Status]
           ,[Name]
           ,[DisplayName])
     VALUES
           ('$ser'
           ,'$name'
           ,'$disname')
GO
"

Invoke-SQLcmd -ServerInstance 'KILIKOOD-PC\MSSQLSERVER,1433' -query $insertquery -U sa -P test123 -Database Fantasy

}

Table ServiceTable looks like below :

Notes : Normally Invoke-SQLcmd should work if you have SQL Server installed in the server as the SQL Server Management Objects will get installed along with it.

If you still have any issues in loading Invoke-SQLcmd , please follow the below steps as referred in the link 

http://www.jasonq.com/blog/2012/3-things-to-do-if-invoke-sqlcmd-is-not-recognized-in-windows-powershell

1. Install SQL Server 2008 R2 Management Objects using Web PI (I'm not sure about versions prior to 2008 R2... if you have more info, please let us know in the comments)
2. Install 'Windows PowerShell Extensions for SQL Server' from the Microsoft® SQL Server® 2008 R2 Feature Pack page (it's about halfway down the page). Make sure you pick the correct flavor for your instance (32 or 64 bit).
3. Run these two commands before calling invoke-sqlcmd in your script:

   Add-PSSnapin SqlServerCmdletSnapin100

   Add-PSSnapin SqlServerProviderSnapin100

For SQL Server 2012, MSFT have switched to a single module. Run this instead:

Import-Module SqlPs