If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Select from UNION into table

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jul 2003
Posts: 50
Select from UNION into table

What am I missing?

I have three tables "UNIONED" and I want the this inserted into a table.

INSERT INTO mytable (A, B, C, D, E)
SELECT A, B, C, D, E
FROM
(SELECT * FROM temp_PARTS1 UNION SELECT * FROM temp_PARTS2)
UNION
(SELECT A, B, C, D, E
FROM a_lot_of_parts)
GROUP BY A,B,C,D,E

This part alone works just like I want it:

(SELECT * FROM temp_PARTS1 UNION SELECT * FROM temp_PARTS2)
UNION
(SELECT A, B, C, D, E
FROM a_lot_of_parts)

I just want it inserted inte stated columns in my table.

I've stared so much at this I'm "homeblind", ie I can't see the forest because of all the trees...
Reply With Quote
  #2 (permalink)  
Old
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,910
Quote:
Originally Posted by oneleg
What am I missing?

I have three tables "UNIONED" and I want the this inserted into a table.

INSERT INTO mytable (A, B, C, D, E)
SELECT A, B, C, D, E
FROM
(SELECT * FROM temp_PARTS1 UNION SELECT * FROM temp_PARTS2)
UNION
(SELECT A, B, C, D, E
FROM a_lot_of_parts)
GROUP BY A,B,C,D,E

This part alone works just like I want it:

(SELECT * FROM temp_PARTS1 UNION SELECT * FROM temp_PARTS2)
UNION
(SELECT A, B, C, D, E
FROM a_lot_of_parts)

I just want it inserted inte stated columns in my table.

I've stared so much at this I'm "homeblind", ie I can't see the forest because of all the trees...
Hi

Does

Code:
  SELECT A, B, C, D, E
FROM
(SELECT * FROM temp_PARTS1 UNION SELECT * FROM temp_PARTS2)
UNION
(SELECT A, B, C, D, E
FROM a_lot_of_parts)
GROUP BY 1, 2, 3, 4, 5
work? You col titles come from the first table in you UNION (i.e. temp_Parts1). Personally, I would avoid using * in unions - they can hide errors that are dead obvious if you explicitely state the column names (e.g. non-union compatible tables).
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #3 (permalink)  
Old
Window Washer
 
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
It would be MUCH better of you told us what you are actually tryin to do. The example isn't a very good one...UNION removes dups for example, and The GROUP BY could be replaced with a DISTINCT. Also you make no mention of constraints.

Post the DDL of your tables, some sample data, and expected results.

Like this (YOu'll notice that the result sets are the same):

Code:
USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(A int, B int, C int, D int, E int)
CREATE TABLE temp_PARTS1(A int, B int, C int, D int, E int)
CREATE TABLE temp_PARTS2(A int, B int, C int, D int, E int)
CREATE TABLE a_lot_of_parts(A int, B int, C int, D int, E int)
GO

INSERT INTO temp_PARTS1(A,B,C,D,E)
SELECT 1,2,3,4,5 UNION ALL
SELECT 6,7,8,9,0

INSERT INTO temp_PARTS2(A,B,C,D,E)
SELECT 11,12,13,14,15 UNION ALL
SELECT 16,17,18,19,20

INSERT INTO temp_PARTS1(A,B,C,D,E)
SELECT 1,2,3,4,5 UNION ALL
SELECT 6,7,8,9,0 UNION ALL
SELECT 1,2,3,4,5 UNION ALL
SELECT 6,7,8,9,0 UNION ALL
SELECT 1,2,3,4,5 UNION ALL
SELECT 6,7,8,9,0 UNION ALL
SELECT 1,2,3,4,5 UNION ALL
SELECT 6,7,8,9,0 UNION ALL
SELECT 1,2,3,4,5 UNION ALL
SELECT 6,7,8,9,0
GO


INSERT INTO mytable99 (A, B, C, D, E)
     SELECT A, B, C, D, E
       FROM ( SELECT * 
		FROM (
		       SELECT * 
			 FROM temp_PARTS1 
			UNION 
		       SELECT * 
			 FROM temp_PARTS2
		      ) AS XXX
               UNION
	      SELECT A, B, C, D, E
		FROM a_lot_of_parts
	    GROUP BY A,B,C,D,E) AS YYY

SELECT * FROM myTable99
GO

TRUNCATE TABLE myTable99
GO


INSERT INTO mytable99 (A, B, C, D, E)
     SELECT A, B, C, D, E FROM temp_PARTS1 UNION
     SELECT A, B, C, D, E FROM temp_PARTS2 UNION
     SELECT DISTINCT A, B, C, D, E FROM a_lot_of_parts

SELECT * FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99, temp_PARTS1, temp_PARTS2, a_lot_of_parts
GO
__________________
Brett
8-)

It's a Great Day for America everybody!

dbforums Yak CorralRadio 'Rita
dbForums Member List
I'm Good Once as I ever was

The physical order of data in a database has no meaning.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jul 2003
Posts: 50
That din't work either... but I found out that this works:

INSERT INTO mytable (A, B, C, D, E)
(SELECT * FROM temp_PARTS1 UNION SELECT * FROM temp_PARTS2)
UNION
(SELECT A, B, C, D, E FROM a_lot_of_parts
GROUP BY A, B, C, D, E)


What's the alternative to using UNION in this case?
Reply With Quote
  #5 (permalink)  
Old
Window Washer
 
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
Quote:
Originally Posted by oneleg
That din't work either... but I found out that this works:

INSERT INTO mytable (A, B, C, D, E)
(SELECT * FROM temp_PARTS1 UNION SELECT * FROM temp_PARTS2)
UNION
(SELECT A, B, C, D, E FROM a_lot_of_parts
GROUP BY A, B, C, D, E)


What's the alternative to using UNION in this case?
Whatdya mean it doesn't work...did you cut and paste the whole thing?

UNION. FYI, removes duplicates....

And I'm still at a loss as to what you're really trying to accomplish....or is this just an exercise?
__________________
Brett
8-)

It's a Great Day for America everybody!

dbforums Yak CorralRadio 'Rita
dbForums Member List
I'm Good Once as I ever was

The physical order of data in a database has no meaning.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jul 2003
Posts: 50
"That didn't work either... " was my response to pootle flump's post...

Yes it's an exercise... an excercise in trying to understand how the French people who designed this Access database thought... I'm moving it to SQL-Server and redesigning it at the same time.

These are the tables involved in this section I'm redesigning.

1. List of parts that are to be priced in i system.

2. Table "a-lot-of-parts" that contains parts that "should be quoted". This may or may not contain all the parts that are to be priced. These table contains a list of parts from certain factories.

3. List of parts to be priced that are found in another system.

4. List of parts to be priced that are NOT found in another system.

It seems like they want to combine these three tables (2,3,4) into one, avoiding dulicates.

The tables used are very big and the data can be considered "secrets" so I'd rather not post any of that..
Reply With Quote
  #7 (permalink)  
Old
Window Washer
 
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
Quote:
Originally Posted by oneleg
"an excercise in trying to understand how the French people who designed this Access database thought...
Are you trying to piss me off?

French people....

Keep your freinds close, but your enemies closer...hmmm wonder which category they fit in?
__________________
Brett
8-)

It's a Great Day for America everybody!

dbforums Yak CorralRadio 'Rita
dbForums Member List
I'm Good Once as I ever was

The physical order of data in a database has no meaning.
Reply With Quote
  #8 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,560
They fall into the "Mostly harmless" category.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On