В Windows есть два модуля для работы с MS SQL сервер через Powershell. Первый SQLPS - он устанавливается вместе с SQL Server и больше не обновляется. Более новый модуль называется SQLServer и устанавливается отдельно. Что бы проверить установлен ли у вас SQLPS нужно запустить следующий командлет:
Get-Command -Module *SQL*
Если вы видите, то мы должны обновить модуль:
Install-Module -Name SqlServer -AllowClobber
Если его нет, то просто устанавливаем с 0 либо на хост, либо на сервер:
Install-Module -Name SqlServer
Мы так же можем запустить консоль powershell с SSMS и если модуля не будет нам об этом скажут:
Для того что бы посмотреть все командлеты модуля нужно выполнить следующую команду:
Get-Command -Module SQLServer
Нам нужна команда powershell Invoke-Sqlcmd, которая может выполнять запросы удаленно так и локально. Т.к. я буду часто обращаться к серверу я сразу объявляю переменные с инстансом и учетными данными. Если вы запустили powershell из SSMS, то вам делать это не нужно. Если же вы запустили консоль удаленно, при каких-то проблемах убедитесь, что все нужные настройки сделаны. Настройки позволяющие удаленно подключаться через SSMS позволяют подключаться и через Powershell.
#Объявляем компьютер\экземпляр SLQ
$ins = "WIN-MJ79T89M7PL\SQLEXPRESS"
#Запрашивается логин и пароль, а затем хранится в переменной текущего сеанса powershell
$cred = Get-Credential
Выполним запрос на получение списка баз данных, для этого есть отдельный командлет:
Get-SqlDatabase -ServerInstance $ins -Credential $cred
После этого выполним обычный SELECT через powershell:
Invoke-Sqlcmd -ServerInstance $ins -Credential $cred -Database pubs -Query "SELECT * FROM authors"
Где:
-ServerInstance - переменная, в которой лежит имя сервера и экземпляра
-Credential - переменная с пользовательскими данными
-Database - имя базы данных (опционально)
-Query - сам запрос
В Powerhsell есть отдельный командлет на чтение данных с MS SQL. В данном случае запрос аналогичен предыдущему:
Read-SqlTableData -ServerInstance $ins -Cred $cred -DatabaseName pubs -SchemaName "dbo" -TableName "authors"
Вывод мы получим в виде "листа". Для того, что бы преобразовать в таблицу есть команда Format-Table или алиас ft :
Invoke-Sqlcmd -ServerInstance $ins -Credential $cred -Database pubs -Query "SELECT * FROM authors" | ft
Для того что бы все время не писать повторяющиеся значения я помещу их в хеш-таблицу:
$params = @{Credential=$cred; Database='pubs'; ServerInstance=$ins}
Можно сам запрос поместить в переменную и переназначать его каждый раз когда нужно:
$quer = "SELECT * FROM authors"
Invoke-Sqlcmd @params -Query $quer | ft
У меня есть SQL запрос, который лежит в файле и я могу его сразу исполнить благодаря ключу -InputFiles. С помощью Powershell я так же могу сразу выгрузить результат SQL запроса в CSV с разделителем в виде ;
Invoke-Sqlcmd @params -InputFile "C:\SqlScript.sql" | Export-Csv -Path C:\SqlResult -Delimiter ";"
Теперь выполню сравнение:
#Объявляем переменные со строковым и численным значением
$somecity = "Oakland"
$contract = 1
#Обращаем внимание на кавычки. Как и в большинстве языков если снаружи одни кавычки, то внутри другие
$quer = "USE pubs SELECT * FROM authors WHERE City='$somecity' and contract=$contract"
Invoke-Sqlcmd -Query $quer @params | ft
Можно передать значения используя format:
$somecity = "Oakland"
$cont = 1
#Переменные передаем через форматирование
$quer = "USE pubs SELECT * FROM authors WHERE City='{0}' and contract={1}" -f $somecity, $cont
Мы можем использовать знак обратного ударения, если у нас длинные командлеты и мы хотим перенести строки (под буквой ё в английской раскладке):
$quer = "USE pubs SELECT * `
FROM authors `
WHERE City='{0}' and contract={1}" -f $somecity, $cont
Мы можем сформировать ответ сразу в визуальном представлении для этого есть Out-GridView. Лучше увидеть это на практике:
$somecity = "Oakland"
$contr = 1
$var1 = "USE pubs SELECT * FROM authors WHERE City='{0}' and contract={1}" -f $somecity, $contr
Invoke-Sqlcmd -Query $var1 @params | Out-GridView
И это даст нам такое представление с возможностью фильтрации:
Выполним запрос через powershell, где вставим данные из CSV в MS SQL. Для начала создам таблицу, где буду хранить данные по своим дискам. Таблица будет состоять из 3 колонок:
$var1 = "CREATE TABLE TestTable3(Name nvarchar(40), Provider nvarchar(50), Root nvarchar(30))"
Invoke-Sqlcmd -Query $var1 @params
Затем экспортирую нужные данные в CSV
Get-PSDrive | Export-Csv -Path psdrive.csv
Создам строку куда потом буду подставлять значения:
$quer = "INSERT INTO TestTable3(Name, Provider, Root) VALUES('{0}', '{1}', '{2}')"
Затем импортирую данные с csv и циклом буду подставлять значения в переменную query и сразу выполнять запрос через powerhsell:
Import-CSV psdrive.csv | ForEach-Object { $data = $quer -f $_.'Name', $_.'Provider', $_.'Root'
Invoke-Sqlcmd @params -Query $data }
Если мы не хотим писать запрос, то можно воспользоваться существующим командлетом Write-SqlTableData:
$Obj2 = [PSCustomObject] @{Name="Test1";Provider="Test2";Root="Test3"}
Write-SqlTableData -SchemaName "dbo" -TableName "TestTable3" -Force -DatabaseName pubs -Credential $cred -ServerInstance $ins -InputData $Obj2
Остальные возможности этих командлетов можно посмотреть через справку:
Get-Help Invoke-Sqlcmd -Examples
...
Подписывайтесь на наш Telegram канал
Теги: #powershell #ms-sql
Telegram
Rss