Replies: 12 comments 12 replies
-
@ajh60 Sure, happy to provide an example. #first connection -- the '-ConnectionName' parameter is key
Open-SqlConnection -SqlServer "a-sql-server" -SqlDatabase "a-database" -ConnectionName "src"
#second connection
Open-PostGreConnection -Server "a-postgre-server" -Database "some-db" -ConnectionName "dst"
#we can see that there are 2 connections
Show-SqlConnection -All #this will list the connection names
Show-SqlConnection -All | Show-SqlConnection #piping the names into Show-SqlConnection to get details
#now for the main event, getting data from MSSQL into PostGre
# the following is simple and assumes that the structure of the data in MSSQL matches the structure of the data in Postgre (so columns are in the same order, same data types, etc...
Invoke-SqlBulkCopy -SourceConnectionName "src" -SourceTable "source-table" -DestinationConnectionName "dst" -DestinationTable "destination-table"
#the output of the above command will tell you how many rows were bulk inserted into the destination connection. There is alot more you can do.. you can use a sourcequery instead of source table, you can provide parameters to that query.. you can do columnMapping, you can determine how large the batch sizes are and whether to be notified with progress. Let me know if you have more questions. |
Beta Was this translation helpful? Give feedback.
-
This is what I'm trying to do.... how do I fit it for SimplySql model and
to have error handling and performance based...
# Define connection strings for MS SQL and PostgreSQL
$mssqlConnectionString = "Server=xxxxx;Database=Lobe;Integrated
Security=True;"
$pgsqlConnectionString =
"Host=fw-ts-lobearch;Port=5432;Username=xxxx;Password=xxxxx;Database=lobe_arch;"
# Create connection to MS SQL Server
$mssqlConn = New-Object System.Data.SqlClient.SqlConnection
$mssqlConn.ConnectionString = $mssqlConnectionString
$mssqlConn.Open()
# Execute a query to retrieve data
$mssqlCmd = $mssqlConn.CreateCommand()
$mssqlCmd.CommandText = "SELECT * FROM Image_Classification_Master"
$mssqlReader = $mssqlCmd.ExecuteReader()
# Create connection to PostgreSQL
$pgsqlConn = New-Object Npgsql.NpgsqlConnection
$pgsqlConn.ConnectionString = $pgsqlConnectionString
$pgsqlConn.Open()
# Prepare the insert command for PostgreSQL
$pgsqlCmd = $pgsqlConn.CreateCommand()
# Loop through the data retrieved from MS SQL Server
while ($mssqlReader.Read()) {
# Prepare the data for insertion into PostgreSQL
$dataToInsert = @()
for ($i = 0; $i -lt $mssqlReader.FieldCount; $i++) {
$dataToInsert += $mssqlReader.GetValue($i)
}
# Define the INSERT query for PostgreSQL
$pgsqlCmd.CommandText = "INSERT INTO myTable VALUES ($dataToInsert)"
$pgsqlCmd.ExecuteNonQuery()
}
# Close the connections
$mssqlConn.Close()
$pgsqlConn.Close()
…On Thu, Aug 29, 2024 at 10:25 AM Mithrandyr ***@***.***> wrote:
The module is supported on both PS5.1 and PS7+
did you try running Get-Help Invoke-SqlBulkCopy ? I try to have as much
documentation included in the module as possibly... there is a parameterset
specifically for running a query to get data on the source side. As for
performance... total amount of records doesn't matter, only the batch
size.. so if each record is large (multiple text/binary columns with sizing
around 1MB each), then you may want a smaller batch size if memory
consumption becomes an issue. The whole point of bulkcopying is to handle
large volumes of data -- try it out and see what works for your scenario.
—
Reply to this email directly, view it on GitHub
<#166 (reply in thread)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/BBL64GY2A6WUDDG4ZTH2YWDZT4VLZAVCNFSM6AAAAABNJKQLQGVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTANBYHAYTMMI>
.
You are receiving this because you were mentioned.Message ID:
***@***.***>
|
Beta Was this translation helpful? Give feedback.
-
I've had a chance to get back to this and now running into this.
# Load data from MSSQL to PostGre
Invoke-SqlBulkCopy -SourceConnectionName "src" -SourceTable
"Image_Classification_Master" -DestinationConnectionName "dst" -Verbose
-BatchSize 5000
Supply values for the following parameters:
VERBOSE: Performing the operation "Execute bulkloading into ''" on target
"dst".
I never see it come back with any messages... I set it to a small number
5000 but nothing returns
…On Mon, Sep 2, 2024 at 11:05 AM Mithrandyr ***@***.***> wrote:
SimplySql removes the need to manually create .NET objects -- so all of
that can be replaced with the sample I provided above.
One you have opened your two connections by the relevant Open-*Connection
cmdlets, then you just use Invoke-SqlBulkCopy - its that simple.
—
Reply to this email directly, view it on GitHub
<#166 (reply in thread)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/BBL64G357WBBUWYUJUBTDPLZUR5CXAVCNFSM6AAAAABNJKQLQGVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTANJSGIZDONI>
.
You are receiving this because you were mentioned.Message ID:
***@***.***>
|
Beta Was this translation helpful? Give feedback.
-
I see this error in Log:
2024-09-16 12:55:37.295 EDT [428] ERROR: invalid byte sequence for
encoding "UTF8": 0x00
2024-09-16 12:55:37.295 EDT [428] CONTEXT: COPY
image_classification_master, line 1, column spoolstarttime
…On Mon, Sep 16, 2024 at 12:09 PM Mithrandyr ***@***.***> wrote:
The -Verbose won't show what you expect (which is, I believe, to see
progress). If you want to see progress indicators, use the -Notify switch
-- that will cause a progressbar to generate with information on how many
records have processed so far. the output of Invoke-SqlBulkCopy will be
the number of records inserted.
The -BatchSize parameter indicates how many records will be sent at a
time, but all the data will be eventually sent. What makes bulk-loading
effecient, is that data is not processed record by record, but rather batch
by batch. Depending on the shape/size of an individual records, different
batchsizes might be more performant, hence why this is a variable that you
can control.
—
Reply to this email directly, view it on GitHub
<#166 (reply in thread)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/BBL64G6KRADY4V6KX6FMMLTZW37EJAVCNFSM6AAAAABNJKQLQGVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTANRWGE3DEMQ>
.
You are receiving this because you were mentioned.Message ID:
***@***.***>
|
Beta Was this translation helpful? Give feedback.
-
I can try that, but how will I be able to handle the invalid data?
…On Mon, Sep 16, 2024 at 4:24 PM Mithrandyr ***@***.***> wrote:
@ajh60 <https://github.com/ajh60> -- start with something very simple.
try bulk copying just one text column (this way you can get confident with
using the module, then try the more complex scenarios and you won't worry
about whether the problem is your usage of the module or something
else...). Working with byte (Binary) data can be complex -- so that error
you are getting is from the destination server telling you that the data is
invalid.
—
Reply to this email directly, view it on GitHub
<#166 (reply in thread)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/BBL64GZOWCE3A42FHS6RZT3ZW45ANAVCNFSM6AAAAABNJKQLQGVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTANRWGM3TSNY>
.
You are receiving this because you were mentioned.Message ID:
***@***.***>
|
Beta Was this translation helpful? Give feedback.
-
okay I'm back to having a table in mssql and that same table defined in
Postgre I'm using the invoke-sqlbulkcopy but I run into this error.
I don't understand the message since both tables have this field.
14:02:42.610 EDT [20212] ERROR: column "itemid" of relation
"qad_item_attribute" does not exist
2024-10-14 14:02:42.610 EDT [20212] STATEMENT: COPY qad_item_attribute
(itemId, Item_attribute, Item_cvalue, item_modified, ItemType, ProdLine,
Domain) FROM STDIN (FORMAT BINARY)
…On Mon, Sep 16, 2024 at 4:51 PM Mithrandyr ***@***.***> wrote:
once you identify exactly what is wrong -- you will have to figure out how
to transform that data before copying. It could be your source and
destination tables do not have the same schema (name/order of columns and
interchangeable data types). Also database vendors sometimes handle data
types (especially the complex ones) differently. So from the error its hard
to know exactly what the issue is, but it might be a data type mismatch.
—
Reply to this email directly, view it on GitHub
<#166 (reply in thread)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/BBL64GZMXAH3WQ72RBKZ22TZW5ADRAVCNFSM6AAAAABNJKQLQGVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTANRWGQYDAOI>
.
You are receiving this because you were mentioned.Message ID:
***@***.***>
|
Beta Was this translation helpful? Give feedback.
-
can you send me another example using a query to pull data? Does the data just append to what I have already loaded? Thanks. source connectionOpen-SqlConnection -SqlServer "xxxxx" -SqlDabatase "Lobe" -ConnectionName "src" destination connectionOpen-PostGreConnection -Server "fw-ts-lobearch" -Database "lobe_arch" -Credential (Get-Credential) -ConnectionName "dst" Load data from MSSQL to PostGreInvoke-SqlBulkCopy -SourceConnectionName "src" -SourceTable "Image_Classification_Master" -DestinationConnectionName "dst" -DestinationTable "myTable" close all connectionsShow-SqlConnection -all | Close-SqlConnection Thank You |
Beta Was this translation helpful? Give feedback.
-
should it handle the Image(mssql) to bytea(PG) field insert?
…On Mon, Jan 13, 2025 at 11:20 AM Mithrandyr ***@***.***> wrote:
@ajh60 <https://github.com/ajh60> -- yes, bulk inserts are always just
that *inserts* and thus they will append to the table.
to use a query with Invoke-SqlBulkyCopy you will use -SourceQuery instead
of -SourceTable
Invoke-SqlBulkCopy -SourceConnectionName "src" -SourceQuery "Select TOP 10 * FROM Image_Classification_Master" -DestinationConnectionName "dst" -DestinationTable "myTable"
—
Reply to this email directly, view it on GitHub
<#166 (reply in thread)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/BBL64G55CLTCLBEDGUW6E232KPRWFAVCNFSM6AAAAABNJKQLQGVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTCOBSGE4TKNY>
.
You are receiving this because you were mentioned.Message ID:
***@***.***>
|
Beta Was this translation helpful? Give feedback.
-
Invoke-SqlBulkCopy -SourceConnectionName "src" -SourceQuery "select * from
image_classification_master where id = '11761783'"
-DestinationConnectionName "dst" -DestinationTable
"image_classification_master" -BatchSize 25000 -Notify
error:
Invoke-SqlBulkCopy : 22021: invalid byte sequence for encoding "UTF8": 0x00
On Mon, Jan 13, 2025 at 11:32 AM Andy Hartman ***@***.***>
wrote:
… should it handle the Image(mssql) to bytea(PG) field insert?
On Mon, Jan 13, 2025 at 11:20 AM Mithrandyr ***@***.***>
wrote:
> @ajh60 <https://github.com/ajh60> -- yes, bulk inserts are always just
> that *inserts* and thus they will append to the table.
>
> to use a query with Invoke-SqlBulkyCopy you will use -SourceQuery
> instead of -SourceTable
>
> Invoke-SqlBulkCopy -SourceConnectionName "src" -SourceQuery "Select TOP 10 * FROM Image_Classification_Master" -DestinationConnectionName "dst" -DestinationTable "myTable"
>
> —
> Reply to this email directly, view it on GitHub
> <#166 (reply in thread)>,
> or unsubscribe
> <https://github.com/notifications/unsubscribe-auth/BBL64G55CLTCLBEDGUW6E232KPRWFAVCNFSM6AAAAABNJKQLQGVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTCOBSGE4TKNY>
> .
> You are receiving this because you were mentioned.Message ID:
> ***@***.***>
>
|
Beta Was this translation helpful? Give feedback.
-
Okay I found the issue in a Sql_variant field not translating to PG. and
have it fixed.
I have another question: can the sql query piece go get all the ID's
related to a certain date like this load to say temp table then issue
another query where tmptable.id = image_classification_master.ID to pull
the full record set
select id from image_classification_master(nolock)
where ImageName LIKE '20240618%'
This would be quicker since ID is the PK to the tables..
Thanks again for your replies, nice tool to use to transfer data..
…On Mon, Jan 13, 2025 at 12:32 PM Mithrandyr ***@***.***> wrote:
@ajh60 <https://github.com/ajh60>
Two things, (1) you can just "reply" to previous discussion on this page
instead of generating a new "comment" everytime (this may help others in
following the issue more easily).
(2) -- copying data from one database to another does require paying
attention to how each database stores data. I would do some research on how
MSSQL stores data in an Image column and how PostGre stores data in a ByteA
column -- in particular making sure that they are equivalent columns. I
would also do a simple test where you copy a single row from MSSQL to
PostGre (and probably a single column) so you can be very clear on which
set of data is giving an issue.
This is all I have offhand.
—
Reply to this email directly, view it on GitHub
<#166 (reply in thread)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/BBL64G6PUHH6U3JQJQXKC432KP2D7AVCNFSM6AAAAABNJKQLQGVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTCOBSGI4DINY>
.
You are receiving this because you were mentioned.Message ID:
***@***.***>
|
Beta Was this translation helpful? Give feedback.
-
Maybe I didn't explain or didn't understand the example.
I want to get all the ID's(PK) using something like this.
INSERT INTO #ToDelete (Id)
SELECT Id
FROM dbo.Image_Classification_Master WITH(NOLOCK)
WHERE ImageName LIKE '20240618%
OPTION (MAXDOP 0);
Then use #ToDelete tbl go hit against Image_Classification_Master tbl using
the ID from both tables to retrieve Select * FROM
Image_Classification_Master...src
This would be a faster lookup than doing this .. Select * FROM
Image_Classification_Master WHERE ImageName LIKE '20240618%'
would that be able to fit in the TOOL?
Thanks
…On Mon, Jan 13, 2025 at 5:06 PM Mithrandyr ***@***.***> wrote:
Yes, the SourceQuery parameter is just that.. a SQL statement and so it
can include where clauses, etc.
# filter values in queryInvoke-SqlBulkCopy -SourceConnectionName "src" -SourceQuery "Select * FROM Image_Classification_Master WHERE ImageName LIKE '20240618%'" -DestinationConnectionName "dst" -DestinationTable "myTable"
# Filter value from variable$DateAsString = "20240618%"
Invoke-SqlBulkCopy -SourceConnectionName "src" -SourceQuery "Select * FROM Image_Classification_Master WHERE ImageName LIKE @search" -DestinationConnectionName "dst" -DestinationTable "myTable" -SourceParameters @{search = $DateAsString}
Glad this is helpful!
—
Reply to this email directly, view it on GitHub
<#166 (reply in thread)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/BBL64G3BIVS6V6N7NEAUDPL2KQ2HBAVCNFSM6AAAAABNJKQLQGVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTCOBSGUZDKMA>
.
You are receiving this because you were mentioned.Message ID:
***@***.***>
|
Beta Was this translation helpful? Give feedback.
-
give requirements above could you show how that would look using -SouceQuery
Thanks again
…On Mon, Jan 13, 2025 at 9:47 PM Mithrandyr ***@***.***> wrote:
@ajh60 <https://github.com/ajh60> -- yes that should work -- -SourceQuery
will take any valid SQL query.
—
Reply to this email directly, view it on GitHub
<#166 (reply in thread)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/BBL64GZCQTJOB2TQP5KK6632KR3DHAVCNFSM6AAAAABNJKQLQGVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTCOBSGY4DQNI>
.
You are receiving this because you were mentioned.Message ID:
***@***.***>
|
Beta Was this translation helpful? Give feedback.
-
Can you give me a sample of how I could have 2 connections the first connection is mssql where I want to pull data and the 2nd connection is a backend PostgreSql DB where I want to load that info
THanks
Beta Was this translation helpful? Give feedback.
All reactions