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

SQL Server 2000 Forums

Transact-SQL (2000)

Union

Author

Topic

TJ
Posting Yak Master

201 Posts

Posted - 2002-04-05 : 11:39:15

Good Morning!

I have two tables that I would like to join together into one table.
The tables are created with distinct criteria. They have the same amount of columns and the same column names. The tempPiggy table will have null values in the DirState column and the tempDirState will have null values in the PiggyState column.

I think a union will work, but I can't seem to get the syntax to work.


select dlrcode,primssn, accptcode, dan, rtn, dirstate, piggystate, stxmtfee, 
  qmsmailbox,QMSTrans, TXVRoyalty, PrepProd
from dbo.temppiggy
union
select dlrcode,primssn, accptcode, dan, rtn, dirstate, piggystate, stxmtfee, 
  qmsmailbox,QMSTrans, TXVRoyalty, PrepProd
from tempdirstate
into tempState

Does anyone have any ideas?

(SQL Server 7.0 SP3)

Thanks!
Teresa

"Someday I'll know enough to help someone else!"

TJ
Posting Yak Master

201 Posts

Posted - 2002-04-05 : 11:47:32

I've answered my own question AGAIN! I'm sorry for posting here. It seems like everytime I do, I come up with the answer shortly after I've asked the question!


select dlrcode,primssn, accptcode, dan, rtn, dirstate, piggystate, stxmtfee, 
  qmsmailbox,QMSTrans, TXVRoyalty, PrepProd
into tempState
from dbo.temptaxpiggy
union
select dlrcode,primssn, accptcode, dan, rtn, dirstate, piggystate, stxmtfee, 
  qmsmailbox,QMSTrans, TXVRoyalty, PrepProd
from dbo.temptaxdirstate

Thanks!
Teresa

"Someday I'll know enough to help someone else!"

izaltsman
A custom title

1139 Posts

Posted - 2002-04-05 : 11:48:35

If values in the tables are always different, you may want to use UNION ALL instead of UNION -- it will work a little faster (as UNION does an additional search for duplicates, which isn't needed in your case).

---------------
Strong SQL Developer wanted in the Boston area. Please e-mail if interested.

Edited by - izaltsman on 04/05/2002 11:51:33

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-04-05 : 11:48:37

this should be fine (without 'into tempState') if you are just trying to display values. If you are trying to get the values into tempState than I think you will have to do the following


INSERT INTO tempStates SELECT * FROM (select dlrcode, primssn, accptcode, dan, rtn, dirstate, piggystate, stxmtfee, qmsmailbox,QMSTrans, TXVRoyalty, PrepProd
from dbo.temppiggy
union
select dlrcode,primssn, accptcode, dan, rtn, dirstate, piggystate, stxmtfee, 
  qmsmailbox,QMSTrans, TXVRoyalty, PrepProd
from tempdirstate)

-------

EDIT: snipers... run...
Graz, where is that sniper image

Edited by - yakoo on 04/05/2002 12:23:56

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-04-05 : 11:54:15

right ... you don't even need to encapsulate the UNION'ed rowset with the SELECT * FROM.

quote:

this should be fine (without 'into tempState') if you are just trying to display values. If you are trying to get the values into tempState than I think you will have to do the following

INSERT INTO tempStates SELECT * FROM (select dlrcode, primssn, accptcode, dan, rtn, dirstate, piggystate, stxmtfee, qmsmailbox,QMSTrans, TXVRoyalty, PrepProd
from dbo.temppiggy
union
select dlrcode,primssn, accptcode, dan, rtn, dirstate, piggystate, stxmtfee,
qmsmailbox,QMSTrans, TXVRoyalty, PrepProd
from tempdirstate)

-------

setBasedIsTheTruepath
<O>

TJ
Posting Yak Master

201 Posts

Posted - 2002-04-05 : 12:14:33

quote:

INSERT INTO tempStates

Does this create the table?

Thanks!
Teresa

"Someday I'll know enough to help someone else!"

TJ
Posting Yak Master

201 Posts

Posted - 2002-04-05 : 12:32:31

quote:

INSERT INTO tempStates SELECT * FROM (select dlrcode, primssn, accptcode, dan, rtn, dirstate, piggystate, stxmtfee, qmsmailbox,QMSTrans, TXVRoyalty, PrepProd
from dbo.temppiggy
union
select dlrcode,primssn, accptcode, dan, rtn, dirstate, piggystate, stxmtfee,
qmsmailbox,QMSTrans, TXVRoyalty, PrepProd
from tempdirstate)

This doesn't work.

I'm not sure I understand what this is doing so I can't debug the syntax error I'm getting now. It's complaining about the right paren.

Thanks!

"Someday I'll know enough to help someone else!"

TJ
Posting Yak Master

201 Posts

Posted - 2002-04-05 : 12:36:07

quote:

UNION ALL

Thank you!!! This works great!!!



select dlrcode,primssn, accptcode, dan, rtn, dirstate, piggystate, stxmtfee, 
  qmsmailbox,QMSTrans, TXVRoyalty, PrepProd
into tempState
from dbo.temptaxpiggy
union all
select dlrcode,primssn, accptcode, dan, rtn, dirstate, piggystate, stxmtfee, 
  qmsmailbox,QMSTrans, TXVRoyalty, PrepProd
from dbo.temptaxdirstate

Teresa

"It's Friday!"

Edited by - tj on 04/05/2002 12:37:40

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources