Truncating All SYSDBA Owned Tables

Whenever I do an import into SalesLogix I put all my source data into a staging database in SQL Server. This allows you to work with your data in a common place and massage & group data via SQL Views and provides a common place to work with your source data no matter what format it was in (CSV, Excel, Access, AS400, dBase, etc). Those people who have attended my TSQL/DTS training will understand what I am talking about here ūüėČ

When testing my import I often have the need to dump all data from my staging database so I can start out empty again and test the entire import (including the loading of the staging database). I use the following statement to truncate all tables in my staging database that are owned by the SYSDBA user (Since the purpose of the staging database is to prep your data for an import into SalesLogix, I tend to create all tables to be owned by the SYSDBA user)

exec sp_MSForEachTable 'IF ''?'' LIKE ''%SYSDBA%'' BEGIN PRINT ''Truncating ?'' TRUNCATE TABLE ? END'

You can easily add additional tables or conditions of tables to exclude to the statement to fit your particular needs.

ABOUT THE AUTHOR

Ryan Farley

Ryan Farley is the Director of Development for Customer FX and creator of slxdeveloper.com. He's been blogging regularly about SalesLogix, now Infor CRM, since 2001 and believes in sharing with the community. His new passion for CRM is Creatio, formerly bpm'online. He loves C#, Javascript, web development, open source, and Linux. He also loves his hobby as an amateur filmmaker.

1 Comment

  1. SQL is much easier to deal with than other datatypes I will admit. Having a SQL database as the "middleman" in an import is a wonderful idea that I wish I had thought of, or come across sooner. I will be using it in the future for sure.

    I’m still learning MSSQL as I go, so I didn’t know that about truncating. I guess I learned my new thing for today. I’ll definately truncate tables but it’ll be rare that I ever use the procedure you defined unless I did have a staging database. Running that on the "blank" or "eval" databases provided by SalesLogix would be lovingly catostrophic. I might try it just to see what would happen.

    Thanks for the response btw. I’d still be logging all of those deletes and having problems with the word truncate.

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!