Column name in a Union all - 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

Column name in a Union all

Author

Topic

In2Minds
Starting Member

17 Posts

Posted - 2014-01-24 : 07:21:59

Hi all

I am new to SQL, this looks a great site to learn from

Am after some help, I have a union all query from 2 databases that I would like to have the same column name returned on it

Can anyone help me?

thanks in advance,
Code is below
SELECT ‘Managed Data’ as Table1,
NLPostedNominalTran.NLPostedNominalTranID, NLNominalAccount.AccountNumber, NLNominalAccount.AccountName, NLNominalAccount.AccountCostCentre, NLNominalAccount.AccountDepartment, SYSAccountingPeriod.PeriodNumber, NLPostedNominalTran.NLNominalTranTypeID, NLPostedNominalTran.TransactionDate, NLPostedNominalTran.GoodsValueInBaseCurrency, NLPostedNominalTran.GoodsValueInDocumentCurrency, NLPostedNominalTran.DocumentCurrencyID, NLPostedNominalTran.ExchangeRate, NLPostedNominalTran.Reference, NLPostedNominalTran.Narrative, NLPostedNominalTran.UserName, NLPostedNominalTran.UserNumber, NLPostedNominalTran.Source, NLPostedNominalTran.UniqueReferenceNumber, NLPostedNominalTran.PostedDate, NLPostedNominalTran.TransactionAnalysisCode, NLPostedNominalTran.OpLock, NLPostedNominalTran.DateTimeCreated, SYSAccountingPeriod.SYSFinancialYearID
FROM Sage6DGMDLive.dbo.NLNominalAccount NLNominalAccount, Sage6DGMDLive.dbo.NLPostedNominalTran NLPostedNominalTran, Sage6DGMDLive.dbo.SYSAccountingPeriod SYSAccountingPeriod
WHERE SYSAccountingPeriod.SYSAccountingPeriodID = NLPostedNominalTran.SYSAccountingPeriodID AND NLNominalAccount.NLNominalAccountID = NLPostedNominalTran.NLNominalAccountID AND ((SYSAccountingPeriod.PeriodNumber In (7,8,9)))
UNION ALL
SELECT ‘BDL’ as Table2,
NLPostedNominalTran.NLPostedNominalTranID, NLNominalAccount.AccountNumber, NLNominalAccount.AccountName, NLNominalAccount.AccountCostCentre, NLNominalAccount.AccountDepartment, SYSAccountingPeriod.PeriodNumber, NLPostedNominalTran.NLNominalTranTypeID, NLPostedNominalTran.TransactionDate, NLPostedNominalTran.GoodsValueInBaseCurrency, NLPostedNominalTran.GoodsValueInDocumentCurrency, NLPostedNominalTran.DocumentCurrencyID, NLPostedNominalTran.ExchangeRate, NLPostedNominalTran.Reference, NLPostedNominalTran.Narrative, NLPostedNominalTran.UserName, NLPostedNominalTran.UserNumber, NLPostedNominalTran.Source, NLPostedNominalTran.UniqueReferenceNumber, NLPostedNominalTran.PostedDate, NLPostedNominalTran.TransactionAnalysisCode, NLPostedNominalTran.OpLock, NLPostedNominalTran.DateTimeCreated, SYSAccountingPeriod.SYSFinancialYearID
FROM Sage200SDBDL.dbo.NLNominalAccount NLNominalAccount, Sage200SDBDL.dbo.NLPostedNominalTran NLPostedNominalTran, Sage200SDBDL.dbo.SYSAccountingPeriod SYSAccountingPeriod
WHERE NLPostedNominalTran.NLNominalAccountID = NLNominalAccount.NLNominalAccountID AND SYSAccountingPeriod.SYSAccountingPeriodID = NLPostedNominalTran.SYSAccountingPeriodID AND ((SYSAccountingPeriod.PeriodNumber In (7,8,9)))

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-01-24 : 11:56:26

When using UNION or UNION ALL, the column names in the first query are picked up and used as column names through out even though you specify other names in below queries.

SELECT 1 as [Integer]
UNION
SELECT 2 as [Int]

The output column will be [Integer] because it is used so in the top most query. It is the same with UNION ALL, EXCEPT, INTERSECT also..

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.

In2Minds
Starting Member

17 Posts

Posted - 2014-01-24 : 12:03:53

Hi SQLSaga

Great stuff

Thanks so much for your help

Really appreciate it!

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-01-24 : 12:17:39

Good Luck :) Always here to help...

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources