Itgenrst004 : issue with UINT datatype between MySQL and SQL Server container with BULK INSERT after CREATE OR REPLACE

[sda]

I have an issue with UINT datatype in a script replicating one-way from MySQL to SQL Server.

My MySql source as an ID int(10) unsigned NOT NULL AUTO_INCREMENT

The source MySQL container is seen by Query Tool as INT

I first execute:

create or replace table tbllog@sqlserver as select * from tbllog@mysql LIMIT 10

to create my table in SQL Server with few records.

The resulting datatype of ID in SQL Server becomes: numeric(10,0)

After this I try to execute:

BULK INSERT into tbllog@sqlserver
( ID
, BeginVerbruik
, EindVerbruik
, GastID
, LocatieID
, KaartNummer
, Result
, BeginDatumTijd
, EindDatumTijd
)
select ID
,      BeginVerbruik
,      EindVerbruik
,      GastID
,      LocatieID
,      KaartNummer
,      Result
,      BeginDatumTijd
,      EindDatumTijd
from   tbllog@mysqlserver
where  ID BETWEEN 698 AND 100000

I get an itgenrst004 error on the datatype:

itgenrst004
The type of the actual data must match the data type of the column ‘id’ in ‘’.
Ensure that the actual data data type (currently ‘UInt32’) in row #1 is cast to the data type (currently ‘decimal’).
id (UInt32/decimal): 698, beginverbruik (Int32/int32): 0, eind…/int32): 0, locatieid (Int32/int32): 0, result (Int32/int32): 0

We see that now Query Tool sees the ID as:

• MySQL: UInt32
• SQL Server: decimal (data type created by the create or replace statement)

Indeed the Uint32 data type does not exists in SQL Server. But Create or Replace statement is able to overcome this whereas the BULK INSERT not.

In my edge case, I can’t use the CREATE OR REPLACE Statement (nor SYNCHRONIZE) because the dataset is too big on the source and I need to implement a home made script that will truncate in small batches the data.

thanks for your help

Ref:

• MySQL creation script here

[forums]

The functionality of bulk insert has been extended to include the same automatic data type conversion between platforms as used in various locations elsewhere. Please try again on release 22.0.481 which will become available in the course of this week on https://releasenotes.invantive.com.

[forums]

This question was automatically closed after at least 2 weeks of inactivity after a possible solution was provided. The last answer given has been marked as a solution.

Please ask a new question via a separate topic if the problem occurs again. Please include a link to this topic in the new question by pasting its URL into the text.