How do I do APPEND FROM in SQL
How do I do APPEND FROM in SQL
(OP)
I've converted an application package in vintage dBase code to SQL, using strictly SPT basic constructs.
old VFP logic:
use arymst && archive
append from armast && current month activities
now I have to do (simplified):
armast && sql cursor
scan
insert into arymst && sql table
endscan
very slow. is there a faster way ?
Steve Yu
old VFP logic:
use arymst && archive
append from armast && current month activities
now I have to do (simplified):
armast && sql cursor
scan
insert into arymst && sql table
endscan
very slow. is there a faster way ?
Steve Yu
RE: How do I do APPEND FROM in SQL
You query from SQL into arymast
then
CODE -->
If your actual goal is to add the data into the original SQL table as data now is in SQL Server. Well, then do that within SQL Server, don't first load all data to VFP
CODE
Chriss
RE: How do I do APPEND FROM in SQL
yes, that's exactly what I was looking for. append one SQL table to another.
In another application, would this work if source is a cursor or plain .dbf and the target is a SQL table ?
Steve Yu
RE: How do I do APPEND FROM in SQL
you can't work on VFP and SQL Server at the same time and join or union data, as either SQL Server or VFP execute the query, never both.
So when your source is a DBF, the way to get it into SQL Server is an updatable workarea/cursor prepared with CURSORSETPROP. That always starts with a SELECT query getting records from the SQL Server table and in case you don't want to update or delete any existing records, you SELECT them by SQLEXEC with a "SELECT * FROM table WHERE 1=0" to get an empty result, do the CURSORSETPROPS to that, then append from the dbf, and finally TABLEUPDATE.
Chriss
RE: How do I do APPEND FROM in SQL
Need help with the syntax:
to append one SQL table to another, SQL command you suggested :
INSERT INTO targettable (targetfieldlist) SELECT sourcefieldlist FROM sourcetable WHERE ...
Question: how do I specify all fields in both tables, since they have identical fields ?
INSERT INTO targettable (*) SELECT (*) FROM sourcetable WHERE ...
seems logical to me, but apparently it did not work; and I can't find any references.
Steve Yu
RE: How do I do APPEND FROM in SQL
I figured out the syntax, but not the solution I was looking for.
Objective: replicate VFP 'append from' command in SQL (concatenate one SQL table to another)
SQL: Insert into targettable select * from sourcetable where ...
Problem 1: can't generate identity ID field in targettable (must use fieldlist, which is exactly we'd like to avoid)
Problem 2: in VFP 'append from', list and order of fields don't have to match at all; I doubt SQL is as forgiving.
Steve Yu
RE: How do I do APPEND FROM in SQL
You can go into the management studio and let it create a typical insert statement for you, with all fields. Then a typical select, with all fields. Then you can put these together for a working "SQL Append". Obviously, you leave out the pk field as a target field to let it be generated in the target table.
"Script table as" also has all options you need:
Chriss
RE: How do I do APPEND FROM in SQL
In a normal (and thus normalized) database every data has its place in one field of one record of one table and doesn't need to move around. You query it into whatever format you need for reporting or display, but storage is non redundant (except for backups, for which there is a strong reason for the redundancy it is).
Of course you still can insert into a table from another, that can have varying reasons, you could store a template of any kind and repeatedly need it.
If you do this append for archiving purposes, you could create an archive on the fly and in parallel to you normal everyday data with several instruments SQL Server offers, like change data capture (CDC). If you're too concerned with reimplemting exactly what you have in VFP with SQL Server, you'll not get forward fast, you'll implement replacements without looking around for new alternatives.
There's much more on the topic you could file under "auditing": https://www.sqlshack.com/sql-server-auditing-best-...
Chriss