copy data from nonstop to sql server via Powershell

[Leumosta]

Hi,
I have data in Nonstop and I want to migrate this data to SQL server
is there any way to do it by Powershell, I already have command to read this data from Powershell, but I don't know now to migrate or copy these data to SQL server .
and if this data huge, can we migrate it these data?.

[potatoqualitee]

You can import any PowerShell object into SQL Server using Write-DbaDbTableData. So if you can get a query out of Nonstop using PS, then you can import line by line but I'd look for other options as this one sounds like it'd be rather incomplete.

[Leumosta]

Thank you Bro potatoqualitee

this the first time I work with PowerShell command i feel it is simple, but because i do not have a time I want to finish this part of my job quickly to save my time and to get good applause from my manager :) to automate this job. because we do it manually.

I hope and really appreciate to help me if you explain or give me the commandto insert this data to SQL server directly from PoewrShell

this the command below to extract the data from Nonstop to DataSet i need the step 2 how i insert this data from DataSet to SQL server

$SqlQuery = "select * from spfppi ;"
$SqlConnection = New-Object Data.Odbc.OdbcConnection
$SqlConnection.ConnectionString = "Dsn=NONSTOPUI_DS;uid=XXXXXXXXX;pwd=XXXXXXXXX;"
$SqlCmd = New-Object System.Data.Odbc.OdbcCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.Odbc.OdbcDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$SqlAdapter.SelectCommand.CommandTimeout = 60000
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

[Leumosta]

I executed the following commands for step two and it was executed and finished without any errors and when I checked the table in SQL server it was empty?!

there is any mistake in the commands?

$SqlServer = 'xxxxxxxx' # SQL Server instance (HostName\InstanceName for named instance)
$Database = 'SARIEArch' # SQL database to connect to
$uid = 'XXXXXXX' # SQL Authentication login
$pwd = 'xxxxxx' # SQL Authentication login password
$sqlTable = 'dbo.MSGTXT_stage1' # SQL Table name
$SqlConnection1 = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection1.ConnectionString = "Server = $SQLServer; Database = $Database; User ID = $uid; Password = $pwd;"
$SqlConnection1.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $SqlConnection1

for ($i = 0; $i -lt $DataSet.Tables[0].Rows.Count; $i++)
{
try{
$valuestr = New-Object system.StringBuilder
for( $vi = 0; $vi -lt 9; $vi++)
{

                        if (vi == 8)
                        {
                            $valuestr.Append("'" + $DataSet.Tables[0].Rows[$i][$vi].ToString().Trim().Replace("'", "/") + "'")
                        }
                        else
                        {
                            $valuestr .Append("'" + $DataSet.Tables[0].Rows[$i][$vi].ToString().Trim().Replace("'", "/") + "',") 
                        }

}
$insertquery="INSERT INTO MSGTXT_stage1 ([ICN],[ICN_NODE],[PARTITION_ID],[ICN_CREATE_DT],[ICN_SEQ_NUM],[ICN_SUBNUM],[ICN_TAG_ID],[ICN_TAG_VER],[BLK_NUM],[BLK_LEN],[FREE_FORM_TXT])
VALUES("+ $valuestr.ToString() + ")"

$Command.CommandText = $insertquery
$Command.ExecuteNonQuery()

}
catch{}
}

[potatoqualitee]

hey @Leumosta - this is not dbatools. You'll probably have better luck on the SQL Slack in the #PowerShell channel https://aka.ms/sqlslack