Insert / Update while copying from Excel to SQL in Talend

Question

I am copying data from Excel to SQL tables using Talend. Before copying I need to check for Primary key's column. If the key is already existing in the database I need to update that record or else insert that record in the database and it will get assigned an auto incremented key.

How do I do this?

Answer 1

You can use the easy but slow way and simply use either the INSERT OR UPDATE or the UPDATE OR INSERT (the difference being whether the update or insert is tried first) option in the txxxSqlOutput component. This is supported by most of the RDBMS components including the SQL Server one:

If you are expecting more updates than inserts then the Update or insert option would be better performing and vice versa.

Alternatively, you can query the database beforehand and then inner join that on the primary key with your data flow to be added to the database. The matches are obviously updates and the rejects (enable Catch inner join reject on another data flow in the tMap) are then obviously inserts.

With the tMap being configured like so:

The second way can be much more performant, especially when upserting large data sets as the first option has to attempt to insert each line, line by line, and then if it fails it then rewrites it as an update statement.

One other benefit of the second is that it can also be contained in a single batch/transaction as you are no longer having to deliberately catch a failed insert/update and then update/insert instead. This means you can make your job a lot more robust and handle errors more appropriately.

Answer 2

Actually i was working on the same problem a few days ago and this is how i worked:

1. Loaded all data from excel to sql temporary tables (The tempory tables must have the same structure as the tables in the database and must be created in the same database)
2. Create a stored procedure that compare primarykey of the tempory table with the database one (Insert if doesn't exist else update)
3. Called the stored procedure in ETL task.

Example of the stored procedure:

   CREATE PROCEDURE [dbo].[up_ImportProductsData]
    AS
    BEGIN
      @Product_PK int,     //Product primary key
      @Product_Name NVarchar(30) // Product name

    // we create a cursor on product temporay table
    DECLARE c_Product CURSOR FOR
    SELECT Product_PK,
           Product_NAME,
    FROM Product_tempory_table

    //We open the cursor to check product pk one by one
    OPEN c_Product CURSOR
    FETCH c_Product CURSOR INTO 
    @Product_PK, @Product_Name 
    WHILE @@fetch_status=0

    //Checking if the Product_PK exits
    BEGIN

    //If the PRimary key doesnt exists then we insert it
     IF NOT EXISTS(SELECT * FROM dbo.Product_Table
    WHERE Product_PK=@Product_PK)
    BEGIN
            INSERT INTO dbo.Product_Table
                    ( 
                       Product_PK,
                       Product_NAME
                    )
            VALUES  ( 

                      @Product_PK, @Product_Name
                    )  
       END

    //If The primary key exists we update it
    ELSE
      BEGIN
         UPDATE dbo.Product_Table
         SET    Product_NAME=@Product_Name 

         WHERE Product_PK= @Product_PK

      END
    FETCH c_Product INTO 
    @Product_PK, @Product_Name 
    END
    CLOSE c_Product
    DEALLOCATE c_Product 

END