SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Insert data into a SQL Server Table using Powershell using Invoke-SQLc

By Vishnu Gupthan,

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

Total article views: 352 | Views in the last 30 days: 8
 
Related Articles
BLOG

A Couple of Invoke-SQLCMD Issues

In working on a PowerShell script to load data into SQL Server, I decided to use the Invoke-SQLCMD c...

BLOG

Invoke-SqlCmd and error results

Don’t trust Invoke-SqlCmd and error resultsI love to call Invoke-sqlcmd from powershell but one mu...

BLOG

Don’t trust Invoke-SqlCmd and error results

Don’t trust Invoke-SqlCmd and error results I love to call Invoke-sqlcmd from powershell but on...

FORUM

Invoke-Sqlcmd and Transactions

How can I impose a transaction context on Invoke-Sqlcmd or emulate Invoke-Sqlcmd using SMO objects?

FORUM

Powershell vs Sqlcmd

Is there a simple explanation of how Powershell and Sqlcmd differ?

Tags
powershell    
 
Contribute