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
-
Take the output of the Get-Services command.
-
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.
$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 : If you have any issues in loading Invoke-SQLcmd , please refer to this link to fix it.
-
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)
-
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).
-
Run these two commands before calling invoke-sqlcmd in your script:
