>>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/In my case I have a list (in a VFP table) of the SQL Server database tables that need to get copied. It's straightforward to SCAN that list, building T-SQL queries
INSERT INTO DestinationDB.dbo.Table SELECT * FROM SourceDB.dbo.Table
which I then run via SQLEXEC( ). Runs well enough for the small databases I'm working with.
Regards. Al
"Violence is the last refuge of the incompetent." -- Isaac Asimov
"Never let your sense of morals prevent you from doing what is right." -- Isaac Asimov
Neither a despot, nor a doormat, be
Every app wants to be a database app when it grows up