cancel
Showing results for 
Search instead for 
Did you mean: 

Data Loading Strategy

ankit_makhija
New Contributor

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 4

Pavol
New Contributor II

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?
Delete table before load

pkona
Former Employee

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
New Contributor III

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
Former Employee

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