Solved

Ignore primary key inserts errors

  • 16 August 2017
  • 8 replies
  • 83 views
R
Badge

Hello,

I am curious of there is a setting or ability to ignore "insert errors" related to primary key constraints in a SQL Database. The workspace writer is doings its job by not allowing duplicate keys to be created but I would like to override/ignore these errors to allow the workspace to continue and finish successfully.

I know I could use an UpdateDetector or reader from the destination to prevent these inserts from hitting the writer but this will really slow down the process to read all existing records to perform this compare.

I was hoping to set a SQL writer or workspace property to allow the workspace to continue when a duplicate key is encoutered.

Any tips or techniques for this situation without actually reading the destination and comparing the input records?

Thanks

 

Ronnie

icon

Best answer by courtney_m 16 August 2017, 22:18

View original

8 replies

C
Badge

Have you tried adding a SQL statement on the writer under the "SQL To Run Before Write" parameter?

You can use a statement such as:

ALTER TABLE <table_name>

NOCHECK CONSTRAINT <PK_Constraint_Name>;

R
Badge

Have you tried adding a SQL statement on the writer under the "SQL To Run Before Write" parameter?

You can use a statement such as:

ALTER TABLE <table_name>

NOCHECK CONSTRAINT <PK_Constraint_Name>;

 

Interesting... that is a neat trick. I have not used this before but to clarify I do not want to disable any constraints I just want the writer and/or workspace to continue when primary key errors are encountered.
C
Badge

Have you tried adding a SQL statement on the writer under the "SQL To Run Before Write" parameter?

You can use a statement such as:

ALTER TABLE <table_name>

NOCHECK CONSTRAINT <PK_Constraint_Name>;

If you are writing to a SQL Server database, you may need to try the following syntax:

 

ALTER INDEX constraint_name ON table_name

 

DISABLE;

 

 

In that case, you may want to add a SQL statement for the "SQL To Run After Write" to enable the constraints again:

 

ALTER INDEX constraint_name ON table_name

 

REBUILD;
C
Badge

Unfortunately, there is no quick way to tell FME to continue if a record fails to write to the database. You would have to previously determine which records are duplicates (using the UpdateDetector or Read from the database and use a duplicate remover based on the primary key).

C
Badge

 

Interesting... that is a neat trick. I have not used this before but to clarify I do not want to disable any constraints I just want the writer and/or workspace to continue when primary key errors are encountered.
Oh, sorry for the misunderstanding. Hopefully no DBA's read this answer ;-)

 

 

erik_jan
Rank
Userlevel 2
Badge +12

Unfortunately, there is no quick way to tell FME to continue if a record fails to write to the database. You would have to previously determine which records are duplicates (using the UpdateDetector or Read from the database and use a duplicate remover based on the primary key).

Or use the SQLExecutor with SQL Select count(*) as PK_EXISTS from table where PK_ID = @Value(PK)

 

assuming the PK_ID is the ID field in the table and PK is the name of the attribute.

 

Then use a Tester to test if PK_EXISTS = 0 and write only if the feature passes the test.

 

R
Badge
Or use the SQLExecutor with SQL Select count(*) as PK_EXISTS from table where PK_ID = @Value(PK)

 

assuming the PK_ID is the ID field in the table and PK is the name of the attribute.

 

Then use a Tester to test if PK_EXISTS = 0 and write only if the feature passes the test.

 

I like this! Good suggestion, it is dynamic and might be a little slower in the pipeline but faster overtime than the UpdateDetector. My worry with updateDetector is overtime this table will be huge and the memory consumption in this transformer would be very large.

 

 

koenterralytics
Rank
Badge +8

I think I found another way to solve your issue which is fast and flexible. Instead of using the sql writer you can use the transformer SQL-executor. When using this transformer you are much more flexible when inserting records. 

For example (postgres syntax):

INSERT INTO customers (NAME, email)
VALUES
   (
      'Microsoft',
      'hotline@microsoft.com'
   ) 
ON CONFLICT ON CONSTRAINT customers_name_key 
DO NOTHING;

When inserting many records this method can become slow. As a work-around I use an aggregator to insert many records at one time (bulk insert). 

0684Q00000ArKLdQAN.png

Reply


Community Stats

28,661
Posts
109,324
Replies
36,528
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.