SQL query: merge 2 tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 2 tables:

TableA
item desc
apple fruit
dog animal

TableB
item desc
cat animal
egg food

What is the SQL query to merge the two tables, and save it into TableC? This
should be the output:
item desc
apple fruit
dog animal
cat animal
egg food
 
Problem though...
How do I save it to a 3rd table named TableC?

Adding "INTO TableC" in the 1st row doesn't work.
 
Anakin said:
I have 2 tables:

TableA
item desc
apple fruit
dog animal

TableB
item desc
cat animal
egg food

What is the SQL query to merge the two tables, and save it into TableC? This
should be the output:
item desc
apple fruit
dog animal
cat animal
egg food
Click to expand...

If TableC is already built, these two separate queries would do it (but
there's probably a better way!)

INSERT INTO TableC SELECT Item, Desc FROM TableA;
INSERT INTO TableC SELECT Item, Desc FROM TableB;
 
Problem though...
How do I save it to a 3rd table named TableC?
Click to expand...

SMartin's two successive append queries is one way; or, you can create
an Append query based on the saved Union query; or you can use a
Subquery:

INSERT INTO TableC
SELECT Item, Desc FROM
(SELECT Item, Desc FROM TableA
UNION
SELECT Item, Desc FROM TableB);

John W. Vinson[MVP]
 
Hi,

While this code works:

SELECT * FROM tbl_MRA_Form_NB
UNION ALL SELECT * FROM tbl_MRA_Form_JPS;

(and it seems to create a UNION QUERY type query object in query object
window)

what I really want is to append the results of this query into a 'new_table'
after having first deleted the data in 'new_table'.

I tried writing the following

INSERT INTO new_table
SELECT * FROM tbl_MRA_Form_NB
UNION ALL SELECT * FROM tbl_MRA_Form_JPS;

but when I try to run it or even save it, all I get is a 'Syntax error in
FROM clause' from Access.

What's going on?
 
Re-read John's SQL.

INSERT INTO new_table
SELECT * FROM
(SELECT * FROM tbl_MRA_Form_NB
UNION ALL
SELECT * FROM tbl_MRA_Form_JPS)

Of course, you really should have field lists there, as oppose to *.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
Of course, a recursion.
But, in my case, running this over time (meaning if the user were to run it
say weekly) would have the effect of adding pre-existing records to the
'new_table' table. What I had in mind, was initially purging the new_table of
any data...or even deleting it before proceding with the code in your mention
below. I would have to think SQL would have some command that would perform
the cleaning up prior to the appending..

But, this is great, I could probably drop this into a macro after a delete
table command.
 
See my November, 2003 "Access Answers" column in Pinnacle Publication's
"Smart Access" for a query that will add records that are missing, or update
matching records. (It won't, however, delete records that no longer exist)

You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)
 
That's an inateresting approach....would there be a SQL command that deletes
data from an existing table that could be applied before the

INSERT INTO new_table
SELECT * FROM
(SELECT * FROM tbl_MRA_Form_NB
UNION ALL
SELECT * FROM tbl_MRA_Form_JPS)

code above got run?
 
DELETE FROM new_table
WHERE ID NOT IN
(SELECT ID FROM tbl_MRA_Form_NB
UNION ALL
SELECT ID FROM tbl_MRA_Form_JPS)

It's far more problematic if you don't have a single field primary key.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
I guess the plot wants to thicken....

In the tables tbl_MRA_Form_NB and JPS, ID is the pk and the same ID will
occur with the frequency of a single record in each on. In the new_table, we
would want a PK comprising ID and also REVINT (meaning reviewer's initials);
we would never have more than two records per ID, one would have REVINT="NB"
and the other having the identical ID would have REVINT="JPS".

I would like to ask a question concerning all this.

Is the following intended to be the entirety of an A2K qry. What 'type' of
query is it to be called as you seem to be able to have different flavors?

DELETE FROM new_table
WHERE ID NOT IN
(SELECT ID FROM tbl_MRA_Form_NB
UNION ALL
SELECT ID FROM tbl_MRA_Form_JPS)

And, given what we know about the constraints records should follow in
new_table, does this new information change things materially?

In any event, much thanks for your help.
 
You know, I thought I would go ahead and create an 'append' query and paste
your suggested SQL code there at which point, on trying to save it, A2K
returned the following bit of 'helpful' verbiage:

This operation is not allowed in subqueries. (Error 3787)
This is an unexpected error. Please contact Microsoft Product Support
Services for more information.

???
 
Yes, that was the entire SQL for the Delete query. However, if ID 123 exists
in new_table and tbl_MRA_Form_NB but not in tbl_MRA_Form_JPS, it will not be
deleted by that query. Instead, you'd need two separate queries:

DELETE FROM new_table
WHERE REVINT = "NB"
AND ID NOT IN
(SELECT ID FROM tbl_MRA_Form_NB)

and

DELETE FROM new_table
WHERE REVINT = "JPS"
AND ID NOT IN
(SELECT ID FROM tbl_MRA_Form_JPS)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
Access User said:
Of course, a recursion.
But, in my case, running this over time (meaning if the user were to run it
say weekly) would have the effect of adding pre-existing records to the
'new_table' table. What I had in mind, was initially purging the new_table of
any data...or even deleting it before proceding with the code in your mention
below. I would have to think SQL would have some command that would perform
the cleaning up prior to the appending..

But, this is great, I could probably drop this into a macro after a delete
table command.
Click to expand...
 
Back
Top