How do I do APPEND FROM in SQL 1

[Steve Yu]

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

 

[Chris Miller]

You query from sql into armast.
You query from SQL into arymast
then

APPEND FROM DBF(Armast)

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

INSERT INTO targettable (targetfieldlist) SELECT sourcefieldlist FROM sourcetable WHERE ...

is valid SQL (also in VFP9, by the way). It's one query.


Chriss

 

[Steve Yu]

Chris,

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

 

[Chris Miller]

Steve,

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

 

[Steve Yu]

Chriss,

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

 

[Steve Yu]

CHris,

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

 

[Chris Miller]

Yes, you have to list all the fields, there is no shortcut. But you don't have to do the tedious work all by yourself.

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

 

[Chris Miller]

In the end, the absence of an APPEND equivalent in SQL points out one thing, that you should consider: You normally won't need such a function.
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-practices

Chriss