How to to make a bulk-insert from SQL Tables into Postgres using Powershell

Question

I have to seed tables in Postgres using Powershell and without the chance to use files. The origin of the information is SQL Server queries.

I find a way to create "automated" insert sentences and the script works, but I'm not satisfied with this solution, and I'm asking for a better way to do this, for example using the query's resultset as input for a bulk insertion into the Postgres tables, but I don't know if is possible and how to do that using Powershell because I'm a newbie in this tool

Here is the code in my current PS1 script:

Import-Module SQLServer

$SQLSERVER = "LAPTOP-201MM1BF"
$MSSQLDB = "origin"
$Username = "dbuser"
$Password = "password"

$Query="BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Declare @target_time_zone varchar(50);
Set @target_time_zone = 'US Eastern Standard Time'; --'Hawaiian Standard Time', 'Alaskan Standard Time', 'Pacific Standard Time', 'US Mountain Standard Time', 'Central Standard Time'

BEGIN TRANSACTION
    SELECT
        ID as source_id,                                                        --Authrize Table's Id
        (SELECT TOP 1 CONCAT_WS( '\', (SELECT @@SERVERNAME), (SELECT DB_NAME()))) as source_facility_id, --Facility's ID
        FINITIALS as source_worker_id,                                          --Worker's id in MRS DB Initials or Employee Id
        null as okta_id,                                                        --Not defined field
        FTITLE as worker_role,                                                  --Worker's title in MRS DB with default value
        FNAME as full_name,                                                     --Worker's Full Name registered in MRS DB with default value
        1 as source_worker_status_id,                                           --All migrated workers will be considered active because in MRS DB doesn't exist this flag
        iif(isnull(FLOADING,0)=0,'false','true') as casepick,                   --Flag to know if the worker is trained to do loading work or not
        iif(isnull(FDROP,0)=0,'false','true') as drops,                         --The training flag is the same than case pick work
        iif(isnull(FFULL,0)=0,'false','true') as fulls,                         --Flag to know if the worker is trained to do full work or not
        iif(isnull(FLOADING,0)=0,'false','true') as loading,                    --Flag to know if the worker is trained to do loading work or not
        iif(isnull(FPUTAWAY_A,0)=0,'false','true') as putaway_a,                --Flag to know if the worker is trained to do putaway air work or not
        iif(isnull(FPUTAWAY_F,0)=0,'false','true') as putaway_f,                --Flag to know if the worker is trained to do putaway floor work or not
        iif(isnull(FRECEIVE,0)=0,'false','true') as receiving,                  --Flag to know if the worker is trained to do receiving work or not
        iif(isnull(FRELO,0)=0,'false','true') as relos,                         --Flag to know if the worker is trained to do relocation work or not
        iif(isnull(FFULL,0)=0,'false','true') as stage_move,                    --The training flag is the same than full work
        iif(isnull(FTUNNELING,0)=0,'false','true') as tunneling,                --Flag to know if the worker is trained to do tunneling work or not
        iif(isnull(FUNLOADING,0)=0,'false','true') as unloading,                --Flag to know if the worker is trained to do unloading work or not
        iif(isnull(ISMOLETRAINED,0)=0,'false','true') as ismoletrained,         --Flag to know if the worker is trained to do operate mole
        [ZONE] as location_id,                                                  --Worker's assigned zone wirh default value
        CONVERT(DATETIME,SQLDATETIME) at time zone @target_time_zone as created,--Creation date
        dateadd(hh, convert(int,left(right(GETDATE() at time zone @target_time_zone,6),3)), GETDATE()) at time zone @target_time_zone as updated
                                                                                --Current datetime as last seen datetime
    FROM
        " + $MSSQLDB + ".[dbo].[AUTHRIZE]   
COMMIT TRANSACTION; 
END"
            
$connectionString = 'Data Source={0};database={1};User ID={2};Password={3}' -f $SQLSERVER,$MSSQLDB,$Username,$Password

$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $connectionString
$sqlConnection.Open()

$ResultSet = Invoke-Sqlcmd -Query $Query -ServerInstance $SQLSERVER

$multiInsert="";
ForEach($record in $ResultSet) {
  $source_id = $record.Item(0)
  $source_facility_id = $record.Item(1) -replace '(^\s+|\s+$)','' -replace '\s+',' ' -replace '''',''''''
  $source_worker_id = $record.Item(2) -replace '(^\s+|\s+$)','' -replace '\s+',' ' -replace '''',''''''
  $worker_role = $record.Item(4) -replace '(^\s+|\s+$)','' -replace '\s+',' ' -replace '''',''''''
  $full_name = $record.Item(5) -replace '(^\s+|\s+$)','' -replace '\s+',' ' -replace '''',''''''
  $casepick = $record.Item(7)
  $drops = $record.Item(8)
  $fulls = $record.Item(9)
  $loading = $record.Item(10)
  $putaway_a = $record.Item(11)
  $putaway_f = $record.Item(12)
  $receiving = $record.Item(13)
  $relos = $record.Item(14)
  $stage_move = $record.Item(15)
  $tunneling = $record.Item(16)
  $unloading = $record.Item(17)
  $ismoletrained = $record.Item(18)
  $created = $record.Item(20)
  $updated = $record.Item(21)

  $OFS = "`r`n"
  $multiInsert= $multiInsert + "INSERT INTO core.worker(source_id, source_worker_id, worker_role, full_name, 
                                casepick, drops, fulls, loading, putaway_a, putaway_f, receiving, relos, stage_move, tunneling, unloading, 
                                moletrained, source_facility_id, created, updated)
                        VALUES ($source_id,'$source_worker_id', '$worker_role', '$full_name', $casepick, 
                                $drops, $fulls, $loading, $putaway_a, $putaway_f, $receiving, $relos, $stage_move, $tunneling, $unloading, 
                                $ismoletrained, '$source_facility_id', '$created', '$updated');" + $OFS;            
}

$sqlConnection.Close()

$PostgreSQLServer = "localhost"
$PPort  = "5432"
$PosgreSQLDB = "destination"
$MyUid = "postgres"
$MyPass = "password"
$DBConnectionString = "Driver={PostgreSQL Unicode};Server=$PostgreSQLServer;Port=$PPort;Database=$PosgreSQLDB;Uid=$MyUid;Pwd=$MyPass;"
$DBConn = New-Object System.Data.Odbc.OdbcConnection
$DBConn.ConnectionString = $DBConnectionString
$DBConn.Open()
$DBCmd = $DBConn.CreateCommand()
$DBCmd.CommandText = $multiInsert
$DBCmd.ExecuteReader()
$DBConn.Close()

I'm unfamiliar with PS and can't speak to that aspect, but on the PG side, the usual bulk-import mechanism is [COPY FROM](https://www.postgresql.org/docs/current/sql-copy.html). You would be using the `STDIN` option to avoid using files. — AdamKG, Nov 04 '20 at 20:48

marsze · Answer 1 · 2020-11-05T13:45:02.547

Here is a simplified version of your code using parameters. It will execute one insert per result row. Also, the use of parameters allows for query plan caching, which might even make it a little faster. And I changed it to use the pipeline, so results will be processed as they arrive.

It might not be perfect yet and of course I cannot really rest it. But I hope it gives you an idea, and you are able to tweak it according to your needs.

# ...open both your sqlserver and postgres connections here...

$DBCmd.CommandText = @"
INSERT INTO core.worker (source_id, source_worker_id, worker_role, full_name, 
        casepick, drops, fulls, loading, putaway_a, putaway_f, receiving, relos, stage_move, tunneling, unloading, 
        moletrained, source_facility_id, created, updated)
VALUES (@source_id, @source_worker_id, @worker_role, @full_name, @casepick, 
        @drops, @fulls, @loading, @putaway_a, @putaway_f, @receiving, @relos, @stage_move, @tunneling, @unloading, 
        @ismoletrained, @source_facility_id, @created, @updated)
"@

Invoke-Sqlcmd -Query $Query -ServerInstance $SQLSERVER | foreach {
    $DBCmd.Parameters.Clear()
    $DBCmd.Parameters.Add("source_id", $_.Item(0))
    $DBCmd.Parameters.Add("source_facility_id", $_.Item(1).ToString().Trim())
    $DBCmd.Parameters.Add("source_worker_id", $_.Item(2).ToString().Trim())
    $DBCmd.Parameters.Add("worker_role", $_.Item(4).ToString().Trim())
    $DBCmd.Parameters.Add("full_name", $_.Item(5).ToString().Trim)
    $DBCmd.Parameters.Add("casepick", $_.Item(7))
    $DBCmd.Parameters.Add("drops", $_.Item(8))
    $DBCmd.Parameters.Add("fulls", $_.Item(9))
    $DBCmd.Parameters.Add("loading", $_.Item(10))
    $DBCmd.Parameters.Add("putaway_a", $_.Item(11))
    $DBCmd.Parameters.Add("putaway_f", $_.Item(12))
    $DBCmd.Parameters.Add("receiving", $_.Item(13))
    $DBCmd.Parameters.Add("relos", $_.Item(14))
    $DBCmd.Parameters.Add("stage_move", $_.Item(15))
    $DBCmd.Parameters.Add("tunneling", $_.Item(16))
    $DBCmd.Parameters.Add("unloading", $_.Item(17))
    $DBCmd.Parameters.Add("ismoletrained", $_.Item(18))
    $DBCmd.Parameters.Add("created", $_.Item(20))
    $DBCmd.Parameters.Add("updated", $_.Item(21))
    $DBCmd.ExecuteNonQuery()
}

# ... close your sqlserver and postgress connections here ...

Thanks for your solution I try to implement but the STDIN call provokes that the SP1 script freezes and is not working... however, your COPY-FROM reference is very interesting — Juan Razo, Nov 05 '20 at 14:47
@JuanRazo The "copy-from" idea was not from me, AdamKG made the comment. I am not sure if you're able to use that tool in this case, because you are copying between two different db server products. — marsze, Nov 05 '20 at 14:49
@JuanRazo You can vote-up answers you like and accept the one that solved your problem. — marsze, Nov 09 '20 at 15:14

How to to make a bulk-insert from SQL Tables into Postgres using Powershell

1 Answers1