正如@jeroen所说，您需要将Insert语句移动到while循环中。下面的代码应该是这样的：
我正在使用PowerShell运行SQL查询。然后，我想根据从查询中提取的信息更新另一个表。我已经在SQLServerManagementStudio中直接测试了SQL查询和更新语句，所以我知道它们可以工作。这些测试的结果表明，有800多条记录需要更新。但是，当我在PowerShell中运行相同的查询和更新时，它只更新一条记录。我主要是从另一个更大的脚本中复制这个脚本，这个脚本是用类似的格式写的。但我似乎错过了ForEach循环(或类似的东西(，但不知道该放在哪里或如何放置。这是我的脚本：
# Set the database connection strings
$Conn02 = new-object System.Data.SqlClient.SqlConnection "SERVER_INFORMATION"
$mySQL02 = New-Object System.Data.SqlClient.SqlCommand
$mySQL02.Connection = $Conn02
$Conn03 = new-object System.Data.SqlClient.SqlConnection "SERVER_INFORMATION"
$mySQL03 = New-Object System.Data.SqlClient.SqlCommand
$mySQL03.Connection = $Conn03
#Connect to the database to perform the query
$Conn02.Open()
$mySQL02.CommandText = "SELECT IDNUM, FNAME, LNAME
FROM TABLE1
WHERE STATUS = 'C'"
$SQL02 = $mySQL02.ExecuteReader()
WHILE($SQL02.Read()){
$NEWID = $SQL02['ID_NUM']
$FNAME1 = $SQL02['FNAME']
$LNAME1 = $SQL02['LNAME']
}
#Run the update
$Conn03.Open()
$mySQL03.CommandText =
"INSERT INTO TABLE2 (user_id,firstname,lastname)
VALUES ('$NEWIDme','$FNAME1','$LNAME1')"
感谢您抽出时间
#Connect to the database to perform the query
$Conn02.Open()
$mySQL02.CommandText = "SELECT IDNUM, FNAME, LNAME
FROM TABLE1
WHERE STATUS = 'C'"
$SQL02 = $mySQL02.ExecuteReader()
#Save reader into datatable
$Datatable = New-Object System.Data.DataTable
$Datatable.Load($SQL02)
#Close the connection
$Conn02.Close()
#Run the update
$Conn03.Open()
foreach($row in $dt){
$NEWID = $row['ID_NUM']
$FNAME1 = $row['FNAME']
$LNAME1 = $row['LNAME']
$mySQL03.CommandText =
"INSERT INTO TABLE2 (user_id,firstname,lastname)
VALUES ('$NEWIDme','$FNAME1','$LNAME1')"
$mySQL03.ExecuteNonQuery()
}
$Conn03.Close()
为了防止SQL注入，我建议在分配值时使用参数
foreach($row in $dt){
$NEWID = $row['ID_NUM']
$FNAME1 = $row['FNAME']
$LNAME1 = $row['LNAME']
$mySQL03.CommandText =
"INSERT INTO TABLE2 (user_id,firstname,lastname)
VALUES (@NEWIDme,@FNAME1,@LNAME1)"
$mySQL03.Parameters.Clear()
$Command.Parameters.AddWithValue('@NEWIDme',$NEWID) | Out-Null
$Command.Parameters.AddWithValue('@FNAME1',$FNAME1) | Out-Null
$Command.Parameters.AddWithValue('@LNAME1',$LNAME1) | Out-Null
$mySQL03.ExecuteNonQuery()
}