Help with simple union - 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

General SQL Server Forums

New to SQL Server Programming

Help with simple union

Author

Topic

Bjcascone
Starting Member

37 Posts

Posted - 2009-05-14 : 10:49:47

I am attempting to union to tables together that do not have any similar fields. Ultimatly i want to make a new table that has both tables combined. this is what i have so far:

select
ChainId, InvoiceId, ItemId, StoreId, '', as Field1, '', as ShipDt,
'', as ChainId, '', as InvoiceId, '', as ItemId, '', as StoreId, Field1, ShipDt,

into #tmp1
from [DB].[Table]

union

select
ChainId, InvoiceId, ItemId, StoreId, '', as Field1, '', as ShipDt,
'', as ChainId, '', as InvoiceId, '', as ItemId, '', as StoreId, Field1, ShipDt,

I have more fields than what is here but this is a good sample.

Thank you,
Brian

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-14 : 10:55:37

whats the purpose of in b/w ''?


select 
ChainId, InvoiceId, ItemId, StoreId, ''  as Field1, ''  as ShipDt, 
''  as ChainId, '' as InvoiceId, '' as ItemId, '' as StoreId, Field1, ShipDt, 

into #tmp1
from [DB].[Table]

union

select
ChainId, InvoiceId, ItemId, StoreId, '' as Field1, '' as ShipDt, 
'' as ChainId, ''  as InvoiceId, '' as ItemId, '' as StoreId, Field1, ShipDt,

Bjcascone
Starting Member

37 Posts

Posted - 2009-05-14 : 11:05:44

the purppose of selecting it into a new table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-14 : 11:13:54

ok then remove , after that and as

Bjcascone
Starting Member

37 Posts

Posted - 2009-05-14 : 11:18:36

??? im confused

Bjcascone
Starting Member

37 Posts

Posted - 2009-05-14 : 11:29:30

I may be starting this all wrong, maybe you have a better way to go about this.
Basically i have two tables. TableA and TableB

TableA has columns: col1, col2, col3, col4
data set(forA) a b c d
TableB has Columns: col5, col6, col7, col8
data set(for B) e f g h
I am looking to create a new table from tables A&B that will look like this:

TableC(NEW) col1, col2, col3, col4, col5, col6, col7, col8
rec 1 a b c d
rec 2 e f g h

Bjcascone
Starting Member

37 Posts

Posted - 2009-05-14 : 11:33:06

ok there was spaces in there and they made that eassier to understand i will try againTableA has columns: col1, col2, col3, col4
data set(forA) -----------a------b-----c-----d

TableB has Columns: col5, col6, col7, col8
data set(for B) ------e----- f--- g -----h
I am looking to create a new table from tables A&B that will look like this:

TableC(NEW) col1, col2, col3, col4, col5, col6, col7, col8
rec 1------- a----- b---- c--- d
rec 2-------------------------------- e---- f ----g ----h

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-14 : 11:34:12

[code]
SELECT col1,col2,col3,col4, CAST(NULL AS datatype of col5),CAST(NULL AS datatype of col6),CAST(NULL AS datatype of col7),CAST(NULL AS datatype of col8)
FROM TableA
UNION
SELECT NULL,NULL,NULL,NULL,col5,col6,col7.col8
FROM tableB
[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-14 : 11:43:20

According to your post on 05/14/2009 11:33:06 you are trying to take tow rows, add columns to each of those rows and then insert that into a new table. Is that correct? If so, what purpose does the UNION serve?

Bjcascone
Starting Member

37 Posts

Posted - 2009-05-14 : 11:55:35

Im not 100% sure. A union is the only way that i know how to combine the contents of 2 tables.
these table just happen to not have any columns that are the same. they both have large record counts on there own and i have to get both of the data sets from each table into a new table that has the combined headers from each of the tables. (new data will eventually be appended into the table that will conform with this new layout from both tables) also any data that is null (or blank in my case) from either one of the tables will be updated from other tables eventually.

If you have another idea i am more than open to alternative solutions. since i am still having trouble with the union i am working on. (there is a total of 50+ columns between the to tables)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-14 : 12:16:51

If you are just doing a select UNION is fine. I though you where trying to insert the results into a new table. If you are tryign to do an INSERT, then just do two inserts. No need to union in that case..

Bjcascone
Starting Member

37 Posts

Posted - 2009-05-14 : 12:28:42

for this:
SELECT col1,col2,col3,col4, CAST(NULL AS datatype of col5),CAST(NULL AS datatype of col6),CAST(NULL AS datatype of col7),CAST(NULL AS datatype of col8)
FROM TableA
UNION
SELECT NULL,NULL,NULL,NULL,col5,col6,col7.col8
FROM tableB

would i have to cast the second select as the appropriate data type?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-14 : 12:44:36

No, just the first select needs the data type.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources