Data Loading Strategy

[ankit_makhija]

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?

[Pavol]

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]

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]

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]

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