I am a newbie to Powershell and below is the scenario where I’m puling SQL data in powershell and inserting records into SQL.
In table1, we have records with series like M100 and its series like “M100-01”,“M100-02”,“M100-03”,“M100-04”,“M100-05”,“M100-06”,“M100-07”,“M100-08”,“M100-09” etc.
Similarly for other M200, M300 etc. records.
I need to pull up al records with are under series like M100-01",“M100-02”,“M100-03” etc and insert new records under single name M100
Below is the logic I used but output does not show any results though DB has values.
$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
$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?