How to add hash table values to SQL Table using Powershell

Question

_{Wednesday, March 25, 2015 3:24 AM}

Hi,

I have sharepoint list with four(column1, column2, column3,column4)columns.I am reading the list column values and adding to hashtable. Now I want to add values from hastable to SQL table with four(column1, column2, colum3,column4)columns using powershell.

I have written the following script for single column but I would like to know how to add values for multiple columns.

            if(($key -eq "Column1") )
            {

               $SqlQuery = "INSERT INTO [TableName] ([Column1]) VALUES ('" + $HashTable.Item($key) +"')"
               #Set new object to connect to sql database
               $connection = new-object system.data.sqlclient.sqlconnection
               $Connection.ConnectionString ="server=SQLServerName;database=SQLDBName;Integrated Security = True;"
          $connection #List connection information 
               $connection.open() #Open Connection
             $Cmd = New-Object System.Data.SqlClient.SqlCommand
             $Cmd.CommandText = $SqlQuery
               $Cmd.Connection = $connection
              $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
              $SqlAdapter.SelectCommand = $SqlCmd
              $DataSet = New-Object System.Data.DataSet
              $SqlAdapter.Fill($DataSet)
              $DataSet.Tables[0]
              $connection.Close()

Can anybody please help me out to accomplish the task? Any help would be greatly appreciated.

Thank you advance.

AA.

All replies (4)

_{Wednesday, March 25, 2015 6:21 AM ✅Answered}

Your SQL setup is not an update query. It cannot work.

You need to use a non-query command to update the table andyou need to insert all columns at the same time.

I suspect you just copied this from somewhere without any understanding of what it is doing.  Helping you will be very difficult as you will need to rewrite your script completely.

I can give you this much but you will have to learn how to use it.

$tmplt=@'
    INSERT INTO [TableName]([Column1],[Column2],[Column3],[Column4]) 
    VALUES('{0}','{1}','{2}','{3}')
'@

$conn=new-object system.data.sqlclient.sqlconnection('server=SQLServerName;database=SQLDBName;Integrated Security = True;')
$conn.open()
$Cmd=$conn.CreateCommand()

foreach($item in $items){
    $cmd.CommandText=$tmplt -f $item.Column1, $item.Column2, $item.Column3, $item.Column4
    $cmd.ExecuteNonQuery()
}

¯\(ツ)_/¯

_{Thursday, March 26, 2015 7:06 AM ✅Answered}

Hi AA,

If you have multiple keys in hashtable, and want to inserts this hashtable into sql table, please refer to the script below, which can loop every key and its value in hashtable and insert value to sql table, please also note I haven't tested:

$HashTable=@{ CA="California";
  "NY" = "New York";
  "IL" = "Illinois"}

$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Data Source=.\sqlexpress;Initial Catalog=Computer;Integrated Security=SSPI;"
$conn.open()
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.connection = $conn

foreach($key in $($HashTable.Keys)){
    $cmd.commandtext = "INSERT INTO  [TableName] ([$key]) VALUES ('" + $HashTable.Item($key) +"')"
    $cmd.ExecuteNonQuery()
}

$conn.close()

If there is anything else regarding this issue, please feel free to post back.

If you have any feedback on our support, please click here.

Best Regards,

Anna Wang

TechNet Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Support, contact [email protected]

_{Wednesday, March 25, 2015 11:08 AM}

Thank you JRV.

As you said, i just copied it from somewhere and i understood what it is doing but i am not sure how to write powershell script for multiple columns as i am new to this.

AA.

_{Monday, March 30, 2015 12:14 PM}

Thank you Very much Anna.

This information is more useful for learners like me.

AA.