Error of Violation Of primary Key.

[Anonymous]

Hello, i am trying to make a transformation using JDBC components and using TMAP component. 
I Want to get information from one database and transform and put it to another.
When the 'new' database table is empty it is ok the insertion of the datas. But im doing some tests and i got the following error:
Violation of PRIMARY KEY constraint 'PK_HJ4'. Cannot insert duplicate key in object 'dbo.HJ4'. The duplicate key value is (3).
I Put this command in the tmap 'String.valueOf( Numeric.sequence( "s1",1,1 ) )' to make a sequencial numero in a field..
The error occurs when by example, in the 'mother table' has 3 values ... i run the process and it insert into the new table 3 values ... 
if i delete some value of the mother table it keeps inserting and mantain the third value in the output table and its ok .. i want that. 
but if i insert a new value in the mother table... when it is updating the output table i got the error that i told before. 
i wanted that if in the output table has 3 values, the insertion 'jumps' to the 4 sequence and keeps inserting..  
is it clear my doubt ? 
anyone can help me please ??
thank you very much.....

[Anonymous]

This is because you are trying to insert a duplicate value into a primary key. The reason you are getting this is because the Numeric.sequence method does not remember what it has done between different batches. It will start from 1 each time you start the job again. The best way around this is to maintain the primary key inside the database. What database are you using? Does it enable you to make use of auto incrementing primary keys? Does it allow you to use sequences? If it does, then I would use one of those methods. If it does not then you can always create a table to use as a sequence and then write a function to update it and get the next one. 
There are lots of ways around this but we would need more information on what you are working with before we can make a viable suggestion.

[Anonymous]

Hello, thank you very much for the reply. 
Well. the database used in this case is SQL SERVER... 
In the database of the output table i cant change anything , the structure
of the primary key is:
HJ4_EMPRES (PK, bigint, not null)  the name of the pk is PK_HJ4.
What more information can I put here to help in this case ?? 
Can I write some code to make this incremental process ??? is there any example of that ??
Do i Use TMAP ?? 
Again, thank you very much for the reply.

[Anonymous]

It is difficult to give you advice on the most elegant way to achieve this as I would need to see your system. But I can give you an idea for how you will definitely be able to achieve this.....but it probably won't be the most efficient or elegant way.
I am assuming that you cannot change anything in the database and can only insert records into this table and query this table. You can probably do a bit more, but given these restrictions the solution I am explaining will work. 
The first thing you need to do is query the table to get the max current ID. Once you have done this, store the value in the globalMap hashmap (tGlobalVar). Now you know the current highest ID in your table. It should be pointed out that I am also assuming that nothing else will be writing to this table during this. If something else is writing, you will need to modify this to retrieve the max ID for every row you are adding. Anyway, you now have the max ID. You can use this variable in a tMap variable that has 1 added to it for every datarow that passes through it. So for every row of data you are sending, the max ID has 1 added to it and that value is assigned to the ID column of your dataset being supplied to the tMSSQLOutput. 
So for every data row you get passed through the tMap, it takes the MAX ID value you retrieved earlier and adds 1 to it. This value is then sent to be inserted with the rest of your datarow. 
It is certainly not the most elegant way of doing this and there are a number of pieces of MS SQL Server functionality that could help you out here (for example,  IDENT_CURRENT( 'table_name' )). But this will get you going. However, do remember that if any other session is inserting to this table, this solution will not work as the ID will get out of sync. Ideally primary keys should be controlled by the database. With Oracle I usually use sequences, with MYSQL I use auto increment keys and with other databases I have made use of the functionality available there. The important thing to think about here is to make sure the (if possible) that getting the next ID and inserting the record happen atomically (preferable and not this example) or without interruption (hopefully for this example to work).
Good luck

[Anonymous]

Hello my friend, me again here hehehe.... 
Thanks for the reply, but I Think this solution is not going to work here in my envirionment, actually i made the tests and it works fine when are just incremental. but i got a situation here that I Don't know how to solve....
Loook,
i get the data from one database (stage area) and make a tmap and put the datas transformed to other database (dataware house)
but the error is this...  i have a key field called HJ4_EMPRES in the dataware house database, if this is incremental it is like :
in the source data base is: 
HJ4_NOME  bla bla bla 
NAME 1
NAME 2
NAME 3
when i run the proccess in the dataware house database staies:
 HJ4_EMPRES        HJ4_NOME  bla bla bla 
       0                   NAME 1
       1                   NAME 2
       2                    NAME 3
     so on ... 
when i update the source data base, everything is updated in the datawared house, ok ? 
but in the scenario like, if i delete a register from the source and run the proccess it will stay like this:
source data base: 
HJ4_NOME  bla bla bla 
NAME 1
NAME 2
dataware house: 
 HJ4_EMPRES        HJ4_NOME  bla bla bla 
       0                   NAME 1
       1                   NAME 2
       2                    NAME 3

BUT IF I ADD OTHER REGISTER IN THE SOURCE, IT OVERWRITES THE DATAWARE HOUSE REGISTER AND STAIES LIKE THIS:
source data base: 
HJ4_NOME  bla bla bla 
NAME 1
NAME 2
NAME OVERWRITE
dataware house: 
 HJ4_EMPRES        HJ4_NOME  bla bla bla 
       0                   NAME 1
       1                   NAME 2
       2                    NAME OVERWRITE

this is my problem ...  ......  could you understand better ?
i'll put some pictures of the process for you to understand better my problem...



  

   1

 2
 3

 4

 5

 6

[Anonymous]

What appears to be happening is that when you delete something from your Source, it's key is being reused (this would only happen if you delete the very newest record though). Then when new data is inserted, it uses the old key. This means that it overwrites your record in the data warehouse. The only way around this is to maintain the ID sequence where it will not be touched. You claim you cannot make any changes to your database. It seems strange that a database that relies on keys to keep data in sync would not have some mechanism in place to allow unique keys to be used like this. It sounds like your Data Warehouse table is not intended to be changed. If this is the case, maybe you can use its MAX ID+1 to ensure that your IDs are unique and not reused.
I would consider having a chat with your DBA to see what they think. I think you may be missing some information and I am not really in a position to give bullet proof advice from what I have been told. Good luck