Help in Union Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Help in Union Query

Author

Topic

dnpmoris
Starting Member

23 Posts

Posted - 2006-09-05 : 17:23:44

Hi

I need help in the beow union query, if any one can modify this query then it would be great help.

INSERT INTO yahoo_ab
Query names are as follows (I need to merge all the queries below into single table):
SELECT_Dnpmoris_yahoo_ab A
SELECT_Dnpmorise_yahoo_ab B
SELECT_Hmorise_yahoo_ab C
SELECT_Jayamorise_yahoo_ab D
SELECT_Morisetti_phani_yahoo_ab E
SELECT_Morisetti_prasad_yahoo_ab G
SELECT_Naveen1305_yahoo_ab H
SELECT_Naveen513_yahoo_ab I
SELECT_Phani_morisetti_yahoo_ab J;

- Naveen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-05 : 21:07:37

Use UNION ALL

INSERT INTO yahoo_ab
Select * from
(
SELECT_Dnpmoris_yahoo_ab A union all
SELECT_Dnpmorise_yahoo_ab B union all
SELECT_Hmorise_yahoo_ab C union all
SELECT_Jayamorise_yahoo_ab D union all
SELECT_Morisetti_phani_yahoo_ab E union all
SELECT_Morisetti_prasad_yahoo_ab G union all
SELECT_Naveen1305_yahoo_ab H union all
SELECT_Naveen513_yahoo_ab I union all
SELECT_Phani_morisetti_yahoo_ab J
) T

Madhivanan

Failing to plan is Planning to fail

dnpmoris
Starting Member

23 Posts

Posted - 2006-09-06 : 12:09:36

INSERT INTO yahoo_ab
Select * from
(
SELECT_Dnpmoris_yahoo_ab union all
SELECT_Dnpmorise_yahoo_ab union all
SELECT_Hmorise_yahoo_ab union all
SELECT_Jayamorise_yahoo_ab union all
SELECT_Morisetti_phani_yahoo_ab union all
SELECT_Morisetti_prasad_yahoo_ab union all
SELECT_Naveen1305_yahoo_ab union all
SELECT_Naveen513_yahoo_ab union all
SELECT_Phani_morisetti_yahoo_ab
);

The above query is not working and saying that join has a problem. So, can anyone help me in modifying this query?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-06 : 12:16:22

Did you notice that T is used as alias name?
Is SELECT_Dnpmoris_yahoo_ab as string or tablename?

Madhivanan

Failing to plan is Planning to fail

dnpmoris
Starting Member

23 Posts

Posted - 2006-09-06 : 14:50:21

The SELECT_Dnpmoris_yahoo_ab is a "Query name". I noticed that 'T' and removed that in my query but still it is not working.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-06 : 14:57:18

It needs the T. Don't remove it.

Tara Kizer

dnpmoris
Starting Member

23 Posts

Posted - 2006-09-06 : 15:05:22

It is warning me that, "Syntax error in JOIN Operation" and pointing to Union All. So, ple help me?

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-09-06 : 17:47:52

I would split this into 2 queries. Access is a bit picky when it comes to complex statements such as this. Firstly, put all your UNION's in a single query and verify that it's returning the right data. Then create another INSERT (or 'Append' as Access likes to call it) query to insert the new data into your table.

Just noticed: you might want to add 'SELECT * FROM ':

INSERT INTO yahoo_ab
Select * from
(
SELECT * FROM SELECT_Dnpmoris_yahoo_ab union all
SELECT * FROM SELECT_Dnpmorise_yahoo_ab union all
SELECT * FROM SELECT_Hmorise_yahoo_ab union all
SELECT * FROM SELECT_Jayamorise_yahoo_ab union all
SELECT * FROM SELECT_Morisetti_phani_yahoo_ab union all
SELECT * FROM SELECT_Morisetti_prasad_yahoo_ab union all
SELECT * FROM SELECT_Naveen1305_yahoo_ab union all
SELECT * FROM SELECT_Naveen513_yahoo_ab union all
SELECT * FROM SELECT_Phani_morisetti_yahoo_ab
) T;

You could also try it without the 'T' alias. I don't like to disagree with Tara, but I've never had to do this with my Access queries.

HTH,

Tim

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-06 : 17:50:06

The T is required for derived tables in T-SQL. I'm not sure about Access.

Tara Kizer

dnpmoris
Starting Member

23 Posts

Posted - 2006-09-06 : 18:56:24

THANKS FOR THIS QUERY AND IT WORKED WELL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources