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.
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.