Jeff's Cluttered Mind

In the Target Table session properties there is an option to Truncate existing data before loading. It is important to realize Informatica does not always use the TRUNCATE function to clear the table, sometimes a DELETE statement is issued, this decision is made at runtime depending on different factors, noted in the table below.

Depending on your needs, TRUNCATE may be what you want because it deletes by deallocating the data pages, freeing storage, and resets Identity column. If the integration service uses DELETE, you may get unexpected results.

You can however issue the TRUNCATE yourself in the target Pre SQL.

Conclusion

Research your database’s TRUNCATE and DELETE properties and review how Informatica handles the Truncate option.

More Information

• warehousewarrior.blogspot.com on Truncate Target
• blog.sqlauthority.com article on Truncate
• mssqltips.com - Truncate vs Dlt

Thank you for your support

or