Powershell scripting to insert records in MSSQL through an SMO Connection
Forum rules
Do not post any licensing information in this forum.
Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
Do not post any licensing information in this forum.
Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
Powershell scripting to insert records in MSSQL through an SMO Connection
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?
Re: Powershell scripting to insert records in MSSQL through an SMO Connection
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:
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:
- $DataSource='c:\test\testdb.accdb'
- $conStr="Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source=$DataSource"
- $conn=New-Object System.Data.OleDb.OleDbConnection($conStr)
- $conn.Open()
- $adcmd = $conn.CreateCommand()
- $adcmd.CommandText='select * from act3'
- $oleadptr=New-Object System.Data.OleDb.OledbDataAdapter($adcmd)
- $cb=New-Object System.Data.OleDb.OleDbCommandBuilder($oleadptr)
- $dt = New-object System.Data.DataTable
- $oleadptr.Fill($dt)
- $id=56
- $displayName='Hello'
- for($i=0; $i -lt 10;$i++){
- $row=$dt.NewRow()
- $row.DisplayName=$displayName
- $row.ID=$id
- $row=$dt.Rows.Add($row)
- }
- $oleadptr.Update($dt)
- $conn.Close()
Re: Powershell scripting to insert records in MSSQL through an SMO Connection
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
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
Re: Powershell scripting to insert records in MSSQL through an SMO Connection
Here is an example of loading into a sql table:
- Attachments
-
- ImportFrom-CsvToSQL.ps1
- (3.41 KiB) Downloaded 153 times