Forum Discussion

ankit_makhija's avatar
ankit_makhija
New Contributor
8 years ago

Data Loading Strategy

Hi, I have been working with SQL server database as target. The loading strategy we use is to trruncate and load the fresh data every day. But we dont have any option for this in SQL Server Insert snap.
As a result I need to put SQL server execute snap at the beginning of my pipeline.

This becomes an issue of the pipelines if the pipeline fails in between as a result my target table is empty till the next run. If we had the option of truncating the table just before inserting the row in DB it would not be a problem.

Please someone can help. If we have a workaround for this?

4 Replies

  • Pavol's avatar
    Pavol
    New Contributor II
    8 years ago

    I have come up with this solution.
    Disadvantage: We are basically copying the entire input data stream, and then throwing it away, just to persuade Snaplogic to do the SQL table deletion just once. There must be another way?

  • pkona's avatar
    pkona
    Former Employee
    8 years ago

    Option 1: If your goal is to ensure that the table is not empty at all times. You can do the following

    insert into a temp table. After the insert is successful (add a tail snap to the output view of the insert snap. The tail snap will wait for its input snap to complete before sending the last n documents to its output view)
    use execute snap to truncate original table (using execute snap)
    use execute snap to insert into original table as select * from temp table (using execute snap)

    Option 2: If you are ok with truncating the table just before you start the insert.

    use the execute snap to truncate table (enable an output view)
    have the rest of the pipeline to read source data and insert into the table

    • nsmith's avatar
      nsmith
      New Contributor III
      8 years ago

      RE: Option 1
      Wouldn’t renaming the tables be faster? I.E. Rename table to table_old and table_temp to table? That’s what we do for MySQL.

      • pkona's avatar
        pkona
        Former Employee
        8 years ago

        Yes. Renaming the table is also an option, instead of “insert into original table as select * from temp table”.