Powershell append to SQL returned Query

Question

I have the following table:

ID|Name |FruitOrder                           
1 |Sarah|Apple, Banana, Orange, Peach, Mangoes
2 |John |Apple, Banana                        
3 |Mary |Peach, Mangoes                       
4 |Mark |Mangoes

I want to take the returned sql table dataset, and append a new column called "note" to the dataset, filling each row with the string "Query1" (for simplicity) :

ID|Name |FruitOrder                            | Note                           
1 |Sarah|Apple, Banana, Orange, Peach, Mangoes | Query1
2 |John |Apple, Banana                         | Query1                        
3 |Mary |Peach, Mangoes                        | Query1                      
4 |Mark |Mangoes                               | Query1

Powershell Code, please see foreach for suggestion.

#powershell code
$SQLDataset = New-Object System.Data.DataSet
$SqlAdapter.fill($SQLDataset) | out-null


foreach ($object in SQLDataset.tables){
# suggestion for logic/code please
}

Do you want to append the dataset in the table or a custom PS object will do? — Vivek Kumar Singh
– Vivek Kumar Singh, Commented Mar 12, 2019 at 14:42
@VivekKumarSingh - I prefer a table. Will need to export to a CSV at the end. Thank you! — LBNHTX
– LBNHTX, Commented Mar 12, 2019 at 16:35

user2883951 · Accepted Answer · 2019-03-12 16:59:40Z

1

Try this:

$newvalue = $SQLDataset |select -expand tables | select ID,Name,FruitOrder,query

this will add the "query" to the array, so you can then set it in your app.

You could then do a:

$newvalue = $newvalue | %{$_.query = "select * from table";$_}

and have your query on each line.

(and yes, I am sure there are easier or even better ways).

answered Mar 12, 2019 at 16:59

user2883951

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Powershell append to SQL returned Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related