×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How do I do APPEND FROM in SQL

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

RE: How do I do APPEND FROM in SQL

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

CODE -->

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

CODE

INSERT INTO targettable (targetfieldlist) SELECT sourcefieldlist FROM sourcetable WHERE ... 
is valid SQL (also in VFP9, by the way). It's one query.


Chriss

RE: How do I do APPEND FROM in SQL

(OP)
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

RE: How do I do APPEND FROM in SQL

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

RE: How do I do APPEND FROM in SQL

(OP)
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

RE: How do I do APPEND FROM in SQL

(OP)
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

RE: How do I do APPEND FROM in SQL

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

RE: How do I do APPEND FROM in SQL

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

Chriss

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close