>I'm working on a project that consolidates VFP data from 4 branch offices into a single SQL Server database. The overall process for each branch is:
>
>1. Push the VFP data into a "SQLTemp" SQL Server database
>2. Lightly data-munge and validate the above
>3. If above is successful, copy everything from SQLTemp to a "SQLStaging" SQL Server database
>4. TRUNCATE the SQLTemp DB
>
>Above 4 steps are performed for all 4 branches.
>
>If data for all 4 branches pass validation, then:
>
>5. Everything in SQLStaging is copied to another "SQLProduction" SQL Server database.
>
>All 3 SQL databases are identical, and live on the same SQL Server instance.
>
>Are there simple and efficient commands or procedures to achieve 3) and 5) above?
>
>3) is the equivalent of VFP APPEND FROM.
>
>I've found one interesting and useful SQL Server stored procedure: sp_MSForEachTable. For instance, to TRUNCATE every table in a SQL Server database you can issue
>
>EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'
>
I'm wondering if something like that can make life easier.
You can generate the script either in SQL Server or even in VFP using textmerge and system tables.
See this blog post for ideas of how to do what you may want.
http://blogs.lessthandot.com/index.php/datamgmt/datadesign/how-to-get-information-about-all-databas/
If it's not broken, fix it until it is.
My Blog