where Name = ‘$item’)” -username $dbUsername -password $dbPassword
$findingsinv=Invoke-Sqlcmd2 -serverInstance $dbInstance -Database $dbName -Query “Insert table1(MetricID,Resource,Type,Details,LastModified)
Select (Select \_ID From table2 where Name = ‘$item’) As ‘MetricID’,d.Resource,d.Type,d.Details,d.LastModified from table1 d
Join table 2 c on c.\_id = d.metricid
where Name like ‘$item%’” -username $dbUsername -password $dbPassword
}
I doubt this condition of Name like ‘$item%’" is not taking the series. I tried multiple ways of giving to pull all series records but just does not work. Can someone assist here?
Thanks!
---
### Author:  [@fredrik-kacsmarck](https://forums.powershell.org/u/fredrik-kacsmarck)
#### Post date: [November 13, 2018, 4:26pm UTC](https://forums.powershell.org/t/need-help-no-result-when-querying-sql-data-in-powershell/11666/2 "2018-11-13T16:26:35Z")
Please use the pre-tags when you post code so it’s easier to read.
Whenever troubleshooting SQL stuff, start with checking that it actually works in e.g. SQL Management Studio or whatever SQL tool you use.
If that doesn’t work then it won’t work using powershell.
Also the first query looks abit weird with (SELECT \_ID From where Name = ‘$item’)
You don’t state the table name there.
So start checking if the query works in the first place.
---
### Author:  [@andy](https://forums.powershell.org/u/andy)
#### Post date: [November 14, 2018, 2:30am UTC](https://forums.powershell.org/t/need-help-no-result-when-querying-sql-data-in-powershell/11666/3 "2018-11-14T02:30:48Z")
You can also use .NET object SqlConnection for all SQL Server operations. Like this:
```
$Sql = "SELECT * FROM [YourTableName] WHERE YouRecordID LIKE 'M100-%'"
$SqlServer = 'SqlServerName'
$SqlDb = 'MyDbName'
$SqlUser = 'SqlServerUser'
$SqlPassword = 'SqlServerPassword'
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$SqlServer;Database=$SqlDb;User ID=$SqlUser;Password='$SqlPassword'"
$SqlConnection.Open()
$SqlCommand = New-Object System.Data.SqlClient.SqlCommand($Sql, $SqlConnection)
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($SqlCommand)
$SqlAdapter.Fill($DataSet) > $null
foreach($table in $DataSet.Tables.GetEnumerator())
{
$table.DefaultView | fl
}
```
See details here: [SqlConnection Class (System.Data.SqlClient) | Microsoft Docs](https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection?view=netframework-4.7.2)
Also you can take a look to my test PowerShell script for execution of SQL Server commands: [https://1drv.ms/u/s!AmLrzNwOB1OBiZxj2n8AiLUY2gkpCw](https://1drv.ms/u/s!AmLrzNwOB1OBiZxj2n8AiLUY2gkpCw)
Feel free for ask any questions about this script
---
### Author:  [@divya](https://forums.powershell.org/u/divya)
#### Post date: [November 14, 2018, 10:19am UTC](https://forums.powershell.org/t/need-help-no-result-when-querying-sql-data-in-powershell/11666/4 "2018-11-14T10:19:40Z")
Thanks Fredrick and Andy for the suggestions!
I should have added more details in my initial email.
I did verify the query in SQL and it works fine and I did use the required connectors as other SQL queries in PS script work fine. Its just the insert statement (below) is not inserting records into DB.
The insert query completes but they are no records inserted and no records in variable $findingsinv. But indeed data exists in DB.
```
$items= @("M100","M200","M300","M400","M500","M600")foreach($itemin$items){Invoke-Sqlcmd2-serverInstance$dbInstance-Database$dbName-Query"Delete from table1 where MetricID = (Select \_ID From table2 where Name = '$item')"-username$dbUsername-password$dbPassword #delcared server instance earlier and this query works fine$findingsinv=Invoke-Sqlcmd2-serverInstance$dbInstance-Database$dbName-Query"Insert table1(MetricID,Resource,Type,Details,LastModified) Select (Select \_ID From table2 where Name = '$item') As 'MetricID',d.Resource,d.Type,d.Details,d.LastModified from table1 d Join table2 c on c._id = d.metricid where Name like '$item-%'"-username$dbUsername-password$dbPassword
```
---
### Author:  [@kvprasoon](https://forums.powershell.org/u/kvprasoon)
#### Post date: [November 14, 2018, 12:42pm UTC](https://forums.powershell.org/t/need-help-no-result-when-querying-sql-data-in-powershell/11666/5 "2018-11-14T12:42:03Z")
Not a DB guy, an update on DBATools.
>
---
### Author:  [@fredrik-kacsmarck](https://forums.powershell.org/u/fredrik-kacsmarck)
#### Post date: [November 14, 2018, 2:24pm UTC](https://forums.powershell.org/t/need-help-no-result-when-querying-sql-data-in-powershell/11666/6 "2018-11-14T14:24:03Z")
Maybe if you try some simple INSERT query as a test first just to see that it actually work?
E.g. as far as I know you state INSERT INTO (you’re missing INTO).
It also might be a good idea to create query as a here-string e.g.
```
# NOTE: the last "@ need to be on a new line and at the beginning of that line, so no indentation.
$query = @"
INSERT INTO table_name (MetricID,Resource,Type,Details,LastModified)
VALUES values_for_whatever_data_types_you_use
"@
```
Except for any parameters that you want to insert in the query using the powershell version of query.
You can pretty much copy/paste from the standard SQL-statement.
Meaning if your statement works in e.g. SQL Management Studio.
Copy that query into a here-string as per example above.
Does it work?
If it does then it’s not an issue with powershell or the cmdlet.
It’s how the query is constructed.
Benefit with the here string is that you write that out to console to check that the query actually looks correct when you run it.
---
### Author:  [@andy](https://forums.powershell.org/u/andy)
#### Post date: [November 14, 2018, 3:35pm UTC](https://forums.powershell.org/t/need-help-no-result-when-querying-sql-data-in-powershell/11666/7 "2018-11-14T15:35:46Z")
For inserting you can use the next code:
```
$sql = @"
INSERT INTO table_name (MetricID,Resource,Type,Details,LastModified)
VALUES values_for_whatever_data_types_you_use
"@
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server='SqlServer';Database='SqlDb';User ID='SqlUser';Password='MyP@SSword'"
$SqlConnection.Open()
$SqlCommand = New-Object System.Data.SqlClient.SqlCommand($Sql, $SqlConnection)
$SQLCommand.CommandText = $Sql
$SqlCommand.ExecuteNonQuery()
```
---
### Author:  [@divya](https://forums.powershell.org/u/divya)
#### Post date: [November 16, 2018, 2:44pm UTC](https://forums.powershell.org/t/need-help-no-result-when-querying-sql-data-in-powershell/11666/8 "2018-11-16T14:44:10Z")
Thanks Fredrick, Prasoon and Andy for the suggestions!
What I noticed was when I give the query with value directly instead of variable like $item, it worked.
This worked: ( but I had to give this statement 6 times, as we have M100-M600)
```
$query6= @"
Insert into tabe1 (MetricID,Resource,Type,Details,LastModified)
Select (Select _ID From table2 where Name = 'M600') As 'MetricID',d.Resource,d.Type,d.Details,d.LastModified from table1 d
Join table2 c on c._id = d.metricid where Name like 'M600%'
"@
$inv =Invoke-Sqlcmd2 -serverInstance $dbInstance -Database $dbName -Query $query-username$dbUsername-password$dbPassword
```
This still does not work when I give as variable $item: No error but values are not inserted into table
```
items = @("M100","M200","M300","M400","M500","M600")
foreach($item in $items)
{
Invoke-Sqlcmd2 -serverInstance $dbInstance -Database $dbName -Query "Delete from table1 where MetricID = (Select _ID From table2 where Name = '$item')" -username $dbUsername -password $dbPassword
$query= @"
Insert into tabe1 (MetricID,Resource,Type,Details,LastModified)
Select (Select _ID From table2 where Name = '$item') As 'MetricID',d.Resource,d.Type,d.Details,d.LastModified from table1 d
Join table2 c on c._id = d.metricid where Name like '$item%'
"@
$inv =Invoke-Sqlcmd2 -serverInstance $dbInstance -Database $dbName -Query $query -username $dbUsername -password $dbPassword
```
---
### Author:  [@fredrik-kacsmarck](https://forums.powershell.org/u/fredrik-kacsmarck)
#### Post date: [November 16, 2018, 2:58pm UTC](https://forums.powershell.org/t/need-help-no-result-when-querying-sql-data-in-powershell/11666/9 "2018-11-16T14:58:03Z")
Instead of running the query e.g. the $inv line (just comment it out).
Then do a Write-Output $query.
Then you can see the whole query without running it, and you can see what the query looks like after the variable have been expanded into it.
---
### Author:  [@dotnVo](https://forums.powershell.org/u/dotnVo)
#### Post date: [May 16, 2024, 8:33pm UTC](https://forums.powershell.org/t/need-help-no-result-when-querying-sql-data-in-powershell/11666/10 "2024-05-16T20:33:39Z")