Server:使用Powershell插入14000+行
Server:使用Powershell插入14000+行
提问于 2019-07-17 17:02:57
我编写了一个SQL查询，将一些服务器属性插入到数据库中。问题是，我现在使用的是14000+服务器，而INSERT命令似乎有1000行限制。我想尽可能少地与数据库联系，无意中发现了Write-SqlTableData，但是我收到了一个错误：
Write-SqlTableData -DatabaseName $SQLDatabaseName -SchemaName dbo
-TableName all.computer -Credential $DatabaseUserCredential
-InputData $sqlValues -ServerInstance "$SQLServerName\MSSQLSERVER"写入-SqlTableData:连接到服务器sqlsrv01.domain.local\MSSQLSERVER失败。
代码片段如下：
#$allComputers = ...retrieved from API
$SqlServerName = 'sqlsrv01.domain.local'
$DatabaseUserCredential = Get-Credential
$sqlValues = New-Object Collections.ArrayList
$allComputers.computers | ForEach-Object {
$_ | ForEach-Object {
$_.PsObject.Properties | ForEach-Object {
If ($_.Value -match "'") {
$_.Value = $_.Value.Replace("'", "''")
}
}
}
$sqlValues.Add("('$($_.aa)', '$($_.phone_number)', '$($_.manual_scan_time)', '$($_.ip_addr)', '$($_.last_logon_user_guid)', '$($_.urlfilter_violated)', '$($_.last_connect_time)', '$($_.id)', '$($_.spyware_detected)', '$($_.arch)', '$($_.sched_start_time)', '$($_.virus_detected)', '$($_.platform)', '$($_.version)', '$($_.manual_aggressive_complete_time)', '$($_.online)', '$($_.type)', '$($_.status)', '$($_.spam_detected)', '$($_.sched_complete_time)', '$($_.sched_scan_time)', '$($_.pop3_scan)', '$($_.manual_aggressive_start_time)', '$($_.last_logon_user_account)', '$($_.name)', '$($_.ss_service)', '$($_.scan_mode)', '$($_.manual_complete_time)', '$($_.mac_addr)', '$($_.components)', '$($_.manual_start_time)', '$($_.last_logon_user_domain)', '$($_.created_at)', '$($_.last_connect_time_human)', '$($_.CustomerName)', '$(Get-Date)')")
}
$SQLQuery = ("USE $SQLDatabaseName`r`n INSERT INTO [dbo].[all.computer] (aa, phone_number, manual_scan_time, ip_addr, last_logon_user_guid, urlfilter_violated, last_connect_time, id, spyware_detected, arch, sched_start_time, virus_detected, platform, version, manual_aggressive_complete_time, online, type, status, spam_detected, sched_complete_time, sched_scan_time, pop3_scan, manual_aggressive_start_time, last_logon_user_account, name, ss_service, scan_mode, manual_complete_time, mac_addr, components, manual_start_time, last_logon_user_domain, created_at, last_connect_time_human, CustomerName, QueryDate)`r`nValues {0}" -f ($sqlValues -join "`r`n,"))
Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $SQLServerName -Credential $DatabaseUserCredential -ErrorAction Stop$allComputers对象是如下所示的对象数组：
aa : True
phone_number :
manual_scan_time : 0
ip_addr : 10.50.0.147
last_logon_user_guid : 286c1410eb470e4ea31dd17e9c1eee31
urlfilter_violated : 0
last_connect_time : 1563379779
id : 0542c82cada09040be79bfb6e54fd119
spyware_detected : 0
arch : x64
sched_start_time : 1562922019
virus_detected : 0
platform : Win Server 2012 R2
version : 6.6.2457/14.1.1516
manual_aggressive_complete_time : 0
online : True
type : 1
status : 1
spam_detected : 0
sched_complete_time : 1562926028
sched_scan_time : 1559294629
pop3_scan : False
manual_aggressive_start_time : 0
last_logon_user_account : jdoe
name : server01
ss_service : True
scan_mode : 0
manual_complete_time : 0
mac_addr : 2B:41:38:B4:34:46
components : {@{version=0; type=1}, @{version=11.000.1006; type=3}, @{version=15.239.00; type=14}}
manual_start_time : 0
last_logon_user_domain : acme
created_at : 1510519578
last_connect_time_human : 7/17/2019 4:09:39 PM
CustomerName : Acme Corp问题如下：
- 我是在正确的轨道上，还是有更好的方法用一个调用将这些行插入到数据库中？
- 知道为什么我不能连接吗？
Write-SqlTableData似乎需要实例名，但是当我使用Invoke-Sqlcmd时，没有它我连接得很好。
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-07-17 18:08:38
我想说的是，“推荐”的方法是把你的批次分成1000批。尽管您的可以一次完成所有的操作，而且有一些解决办法，但我不会这么做，因为您最终可能会遇到其他问题(例如内存和查询大小限制)，特别是，因为您插入的数据是“相当大”的每一行。
要做到这一点，只需使用for循环：
for($i=0; $i -lt $sqlValues.Count; $i+=1000){
Write-Host "Loop: $i"
$SQLQuery = ("USE $SQLDatabaseName`r`n INSERT INTO [dbo].[all.computer] (aa, phone_number, manual_scan_time, ip_addr, last_logon_user_guid, urlfilter_violated, last_connect_time, id, spyware_detected, arch, sched_start_time, virus_detected, platform, version, manual_aggressive_complete_time, online, type, status, spam_detected, sched_complete_time, sched_scan_time, pop3_scan, manual_aggressive_start_time, last_logon_user_account, name, ss_service, scan_mode, manual_complete_time, mac_addr, components, manual_start_time, last_logon_user_domain, created_at, last_connect_time_human, CustomerName, QueryDate)`r`nValues {0}" -f ($sqlValues[$i..($i+999)] -join "`r`n,"))
Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $SQLServerName -Credential $DatabaseUserCredential -ErrorAction Stop
}其中，魔术将选择1000行批，其中：
$sqlValues[$i..($i+999)]PowerShell是解决这种问题的好锤子。另一种更适合于这样的任务的方法是使用(SSIS)，这种服务被设计用来摄取大量不同的数据(包括API)和“提取转换负载”(，ETL)到高性能的数据库。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/57080954
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号