WBDataDiff unexpected behavior with ignoreColumns

[Andreas Emrich]

Hi,

im a little bit confused about the actual behavior of the WBDataDiff combined with the -ignoreColumns Parameter.

WbDataDiff -referenceProfile="- MainDB -"
             -targetProfile="- TargetDB -"
             -referenceTables=myTable
             -file=c:\temp\datasets.sql
             -includeDelete=True
             -excludeTables=T_PROD_*
             -ignoreColumns="idBD,dateactBD,useractBD,dateBD,userBD"
             -excludeIgnored=true
             -singleFile=true
             -referenceSchema=MySchema
             -targetSchema=MySchema

I have two datasets in the MainDB. And one dataset in the TargetDB.

The dataset already existing in the TargetDB are absolote identically to the MainDB excepted the Columns i have stated in the -ignoreColumns.

 

But in the Script that will be generated are two inserts statements for both datasets existing in the MainDB and one to delete the existing dataset in the TargetDB.

I would expect, that WBDataDiff recognize the identicall datasets, because the -ignoreColumns parameter told WBDataDiff to dont compare this columns!

Am i wrong with this?

-- -------------------

-- INSERTS for TargetDB.MySchema.myTable

-- -------------------

insert into MySchema.myTable (nrBD,indexBD,txtBD,activeBD) values (N'115xxx63',3,N'951 XXX xxx 3-xx XXXX',1);

insert into MySchema.myTable (nrBD,indexBD,txtBD,activeBD) values (N'115xxx91',1,N'951 xxx-2X XXXXXXX',1);

-- No UPDATEs for myTable necessary

COMMIT;

-- ---------------

-- DELETE statements

-- ---------------

DELETE FROM MySchema.myTable WHERE idBD = 7;

COMMIT;

ö

And by the way one Question: is it possible to ignore columns with a wildcard like this (to except them in many tables):

-ignoreColumns="id*, date*"

Produktname: Microsoft SQL Server
Produktversion: 14.0
Produktinformationen: Microsoft SQL Server 2017
Treibername: Microsoft JDBC Driver 7.2 for SQL Server
Treiberklasse: com.microsoft.sqlserver.jdbc.SQLServerDriver
Treiberversion: 7.2.2.0

Best regards

Andreas

[Thomas Kellerer]

Can you show me the complete CREATE TABLE statement for mytable (including the primary key)?

Thomas

Andreas Emrich schrieb am 07.08.2019 um 09:50:
> Hi,
>
> im a little bit confused about the actual behavior of the WBDataDiff combined with the -ignoreColumns Parameter.
>
> WbDataDiff -referenceProfile="- MainDB -"
>              -targetProfile="- TargetDB -"
>              -referenceTables=myTable
>              -file=c:\temp\datasets.sql
>              -includeDelete=True
>              -excludeTables=T_PROD_*
>              -ignoreColumns="idBD,dateactBD,useractBD,dateBD,userBD"
>              -excludeIgnored=true
>              -singleFile=true
>              -referenceSchema=MySchema
>              -targetSchema=MySchema
>
> I have two datasets in the MainDB. And one dataset in the TargetDB.
> The dataset already existing in the TargetDB are absolote identically to the MainDB excepted the Columns i have stated in the -ignoreColumns.
>
> But in the Script that will be generated are two inserts statements for both datasets existing in the MainDB and one to delete the existing dataset in the TargetDB.
>
> I would expect, that WBDataDiff recognize the identicall datasets, because the -ignoreColumns parameter told WBDataDiff to dont compare this columns!
> Am i wrong with this?
>
> -- -------------------
> -- INSERTS for TargetDB.MySchema.myTable
> -- -------------------

> insertintoMySchema.myTable (nrBD,indexBD,txtBD,activeBD) values(N'115xxx63',3,N'951 XXX xxx 3-xx XXXX',1);
> insertintoMySchema.myTable (nrBD,indexBD,txtBD,activeBD) values(N'115xxx91',1,N'951 xxx-2X XXXXXXX',1);

>
> -- No UPDATEs for myTable necessary
>
> COMMIT;
>
> -- ---------------
> -- DELETE statements
> -- ---------------
> DELETEFROMMySchema.myTable WHEREidBD =7;
>
>
> COMMIT;
>
> ö
>
> And by the way one Question: is it possible to ignore columns with a wildcard like this (to except them in many tables):
> -ignoreColumns="id*, date*"
>
>

[Andreas Emrich]

Hi,

here are the table sourcecode from SQL Workbench from the MainDB:

CREATE TABLE MySchema.myTable
(
   idBD       bigint identity   NOT NULL,
   nrBD       nvarchar(18)      NOT NULL,
   indexBD    int               NOT NULL,
   txtBD      nvarchar(80)      NOT NULL,
   activeBD   bit               CONSTRAINT DF_myTable_activeBD DEFAULT ((1)) NOT NULL,
   dateactBD  datetime          NOT NULL,
   useractBD  nvarchar(20)      NOT NULL,
   dateBD     datetime          NOT NULL,
   userBD     nvarchar(20)      NOT NULL
);

ALTER TABLE MySchema.myTable
  ADD CONSTRAINT PK_myTable
  PRIMARY KEY NONCLUSTERED (idBD);

ALTER TABLE MySchema.myTable
   ADD CONSTRAINT AK_myTable UNIQUE (nrBD, indexBD);

And the table sourcecode from the TargetDB:

CREATE TABLE MySchema.myTable
(
   idBD       bigint identity   NOT NULL,
   nrBD       nvarchar(18)      NOT NULL,
   indexBD    int               NOT NULL,
   txtBD      nvarchar(80)      NOT NULL,
   activeBD   bit               DEFAULT ((1)) NOT NULL,
   dateactBD  datetime          NOT NULL,
   useractBD  nvarchar(20)      NOT NULL,
   dateBD     datetime          NOT NULL,
   userBD     nvarchar(20)      NOT NULL
);

ALTER TABLE MySchema.myTable
  ADD CONSTRAINT PK_myTable
  PRIMARY KEY NONCLUSTERED (idBD);

ALTER TABLE MySchema.myTable
   ADD CONSTRAINT AK_myTable UNIQUE (nrBD, indexBD);

Best Regards

Andreas

  

[Thomas Kellerer]

Hmm, I can not reproduce this.

I created those tables in two different databases.

In the first one ("referencedb") I ran the following insert:

insert into mytable
(nrbd, indexbd,txtbd,activebd,dateactbd,useractBD,dateBD,userbd)
values
('1',1,'foo',1,'2019-01-01 00:00:00','arthur','2019-01-02 00:00:02','arthur'),
('2',2,'bar',0,'2019-02-01 00:00:00','ford','2019-02-02 00:00:02','ford');

and in the second one ("targetdb"), I only inserted the first row.

Then I ran:

WbDataDiff -referenceProfile='....'
-targetProfile='....'

-referenceTables=myTable
-file=c:\temp\datasets.sql
-includeDelete=True

-ignoreColumns="idBD,dateactBD,useractBD,dateBD,userBD"
-excludeIgnored=true
-singleFile=true
-referenceSchema=dbo
-targetSchema=dbo

The resulting script then contained (as expected):

-- -------------------
-- INSERTS for targetdb.dbo.myTable
-- -------------------
INSERT INTO myTable
(nrBD, indexBD, txtBD, activeBD)
VALUES
(N'2', 2, N'bar', 0);

-- No UPDATEs for myTable necessary

COMMIT;

-- No DELETEs for myTable necessary


This is with SQL Server 2016, but that should not make a difference.
The JDBC driver version is 7.2.1.0

Is it possible that the two profiles use different driver versions?

Andreas Emrich schrieb am 12.08.2019 um 14:56:
> Hi,
>
> here are the table sourcecode from SQL Workbench from the MainDB:
>
> |
> CREATE TABLE MySchema.myTable
> (
>    idBD       bigint identity   NOT NULL,
>    nrBD       nvarchar(18)     NOT NULL,
>    indexBD    int              NOT NULL,
>    txtBD      nvarchar(80)     NOT NULL,

>    activeBD   bit               CONSTRAINT DF_myTable_activeBD DEFAULT ((1))NOT NULL,

>    dateactBD  datetime          NOT NULL,
>    useractBD  nvarchar(20)     NOT NULL,
>    dateBD     datetime          NOT NULL,
>    userBD     nvarchar(20)     NOT NULL
> );
>
> ALTER TABLE MySchema.myTable
>   ADD CONSTRAINT PK_myTable
>   PRIMARY KEY NONCLUSTERED (idBD);
>
> ALTER TABLE MySchema.myTable
>    ADD CONSTRAINT AK_myTable UNIQUE (nrBD,indexBD);
> |
>
>
>
> And the table sourcecode from the TargetDB:
>
> |
> CREATE TABLE MySchema.myTable
> (
>    idBD       bigint identity   NOT NULL,
>    nrBD       nvarchar(18)     NOT NULL,
>    indexBD    int              NOT NULL,
>    txtBD      nvarchar(80)     NOT NULL,

>    activeBD   bit               DEFAULT ((1))NOT NULL,

>    dateactBD  datetime          NOT NULL,
>    useractBD  nvarchar(20)     NOT NULL,
>    dateBD     datetime          NOT NULL,
>    userBD     nvarchar(20)     NOT NULL
> );
>
> ALTER TABLE MySchema.myTable
>   ADD CONSTRAINT PK_myTable
>   PRIMARY KEY NONCLUSTERED (idBD);
>
> ALTER TABLE MySchema.myTable
>    ADD CONSTRAINT AK_myTable UNIQUE (nrBD,indexBD);
> |
>

> Best Regards
> Andreas

[Andreas Emrich]

Oooohhhh... Oohhh,

*Facepalm*

im so sorry... Of course, i think, the script is doing the job correct.

Even when i exclude the column idBD (which is the primary key) the WBDataDiff-script needs this column to reference the corresponding dataset.

And as i wrote in my first Post:

 

The dataset already existing in the TargetDB are absolote identically to the MainDB excepted the Columns i have stated in the -ignoreColumns.

Identically, excepted the idBD (the primary key) inter ali.

And the primary keys are not identically. *Facepalm again*

So i have to compare the datasets in another way.

Thank you for your feedback.

Best regards

Andreas

[Thomas Kellerer]

you can define an alternate (unique) key to be used to compare the rows using the -alternateKey parameter.

e.g. -alternateKey="mytable=indexBD"

Or whatever makes sense for that table.

Thomas

[Andreas Emrich]

Works great.

Thank you very much for that awesome application!

Best regards

Andreas

[ACH114 frfrrffrf]

Your tool is very usefull thanks you.

Kind regards

ACH114 frfrrrffrf