Powershell scripting to insert records in MSSQL through an SMO Connection

[dakota]

I have a set of records in $result and i want to insert these records in a table available in MSSQL through an SMO Connection having authentication(username and password). I have tried many things but not able to insert it into the table.How to execute the above scenario? Can someone help me with scripting?

[jvierra]

We wouldn't use SMO to update a table, SMO is designed to be used for managing a database. For updates we would use the OleDBClient.

It takes about 20 lines of standard SQL code to update a table from an object.

There are dozens of blog examples available if you search. Here is one example. Change the provider to MySql and it should work:

Code: [Select all] [Expand/Collapse] [Download] (Untitled.ps1)

1. $DataSource='c:\test\testdb.accdb'
2. $conStr="Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source=$DataSource"
3. $conn=New-Object System.Data.OleDb.OleDbConnection($conStr)
4.  
5. $conn.Open()
6. $adcmd = $conn.CreateCommand()
7. $adcmd.CommandText='select * from act3'
8. $oleadptr=New-Object System.Data.OleDb.OledbDataAdapter($adcmd)
9. $cb=New-Object System.Data.OleDb.OleDbCommandBuilder($oleadptr)
10.  
11. $dt = New-object System.Data.DataTable
12. $oleadptr.Fill($dt)
13.  
14. $id=56
15. $displayName='Hello'
16. for($i=0; $i -lt 10;$i++){
17.     $row=$dt.NewRow()
18.     $row.DisplayName=$displayName
19.     $row.ID=$id
20.     $row=$dt.Rows.Add($row)
21. }
22. $oleadptr.Update($dt)
23. $conn.Close()

[dakota]

Hi jvierra,

Thanks for the reply!!!
But I need scripting for MSSQL and not MySQL with Authentication. If you could guide me on this , then please suggest?

Regards,
Dakota

[jvierra]

Change OleDb to Sql and it is for SQS.

[jvierra]

Here is an example of loading into a sql table: