SELECT works but INSERT INTO SELECT doesn't

[mud2]

Given tables U1, U2, and U3. Each with one column, with same name and type (char).
Under an ACCESS query in SQL view,
SELECt * from U1 UNION SELECT * FROM U2; works fine,
BUT (As usual in access...)
INSERT INTO U3 SELECT * FROM U1 UNION SELECT * FROM U2; Does not. Produces an "Error in FROM..."

[stanl]

Any form of insert/update/delete that involves multiple tables or linked tables requires they have a primary key. Don't know if that if your issue, but given the information you provided that would be a good guess.

Stan

[CreganTur]

Have you tried explicitly listing the field names, instead of using the asterisk?

[mud2]

Stan: I'll try!
Randy: I've used every (?) permutation of field names...(U1.Myname, etc.), Paranthesis around every/anything....

[mud2]

I rewrote the tables, using indices...still didn't work!

[Demosthine]

Hey there.

If you can provide a sample workbook with what you already have, it will allow us to replicate the exact conditions of your problem rather than guessing at how your tables and queries are set up.

Thanks.
Scott

[CreganTur]

You know... I've been doing some thinking. The issue could be the fact that you're doing an INSERT INTO query using a subquery, and I bet you're doing it in ANSI 89! If you change your database to work with ANSI 92 I'd bet a hundred caps (yes, I'm a Fallout geek :razz2: ) that your query will mystseriously work.

To change your DB to work with ANSI 92 click Tools -> Options -> Tables/Queries -> click the 'this database' checkbox under the 'SQL Server Compatible Syntax (ANSI 92)' header, and BINGO, Bob's yer Uncle:bouncy:

[mud2]

Randy: Thanks for following...but it didn't work!

[Demosthine]

Good Evening.

As far as I know (and I would love for someone to prove me wrong), Access does not actually support the INSERT INTO using a UNION subquery, regardless of ANSI 89 vs ANSI 92. Good suggestion, though, Cregan. That has caused me more than a few issues in the past.

I have two slight workarounds, though. That's the good news.

Method 1:
As it would appear the UNION is a simple Query (no parameters), create a Query of it's own with just:


SELECT * FROM U1
UNION ALL SELECT * FROM U2;


Next, create the INSERT INTO Query:


INSERT INTO U3
SELECT *
FROM UNIONQUERY;


Using this method, Table [U3] must already exist and the records will be appended to the Table.


Method 2:
Using this method, Table [U3] does not have to exist. If it does not, Access will create the Table for you. If it does exist, Access will DROP the Table and then create a new Table with the selected records.



SELECT * INTO U3
FROM (SELECT * FROM U1
UNION ALL SELECT * FROM U2) AS UNIONTABLE;


Either way, by default in Access, it will ask if you are sure you want to paste these X rows into the new Table.

I hope this at least gives you a solution to your problem.
Scott

[mud2]

To Demosthine: Thanks for your suggestions. Both methods work!
Now to go on......

[egac1967]

Hello Scott:

i have a similar issue in MS Access 2007, and I was hoping you could enlighten me a little.

In a MS Access 2K7 form, I have a button that when clicked on, triggers an INSERT INTO table3 (already created with fields since all 3 tables have differente fields) from a SELECT table1.fields, table2.fields, FROM table1, table2 WHERE these 2 tables have the same field x match text in forms!form1!control.

When button is clicked, an error displays "Missing (;) at end of sql statement"; here is "code":

strSQL = "INSERT INTO [tmpDesktop Info] VALUES ([Service Tag], PCPO, PCDate, PCRAM, MSOVer, MSOPO)" _
& "SELECT [Desktop Info].[Service Tag], [Desktop Info].[HW Ship Date], [Desktop Info].[HW PO #], [Department Inventory].[Memory], [Department Inventory].[Current Ver Installed]" _
& "FROM [Department Inventory], [Desktop Info]" _
& "WHERE (([Desktop Info].[Service Tag])= & ' [Forms]![Form1]![PCST] ' & AND [Department Inventory].[PCST] = & ' [Forms]![Form1]![PCST]))' &;"

I've also tried the Insert Into for each Select but still the same error.:doh:

:help :dunno : pray2:

Please help! :)

Thanx,
Egac1967



Hey there.

If you can provide a sample workbook with what you already have, it will allow us to replicate the exact conditions of your problem rather than guessing at how your tables and queries are set up.

Thanks.
Scott

[egac1967]

Never mind, I just found the syntax problem and solved it; but thanx any ways!


Hello Scott:

i have a similar issue in MS Access 2007, and I was hoping you could enlighten me a little.

In a MS Access 2K7 form, I have a button that when clicked on, triggers an INSERT INTO table3 (already created with fields since all 3 tables have differente fields) from a SELECT table1.fields, table2.fields, FROM table1, table2 WHERE these 2 tables have the same field x match text in forms!form1!control.

When button is clicked, an error displays "Missing (;) at end of sql statement"; here is "code":

strSQL = "INSERT INTO [tmpDesktop Info] VALUES ([Service Tag], PCPO, PCDate, PCRAM, MSOVer, MSOPO)" _
& "SELECT [Desktop Info].[Service Tag], [Desktop Info].[HW Ship Date], [Desktop Info].[HW PO #], [Department Inventory].[Memory], [Department Inventory].[Current Ver Installed]" _
& "FROM [Department Inventory], [Desktop Info]" _
& "WHERE (([Desktop Info].[Service Tag])= & ' [Forms]![Form1]![PCST] ' & AND [Department Inventory].[PCST] = & ' [Forms]![Form1]![PCST]))' &;"

I've also tried the Insert Into for each Select but still the same error.:doh:

:help :dunno : pray2:

Please help! :)

Thanx,
Egac1967