Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Select UNION add database name

Posted on 2013-12-09
6
Medium Priority
?
406 Views
Last Modified: 2013-12-10
I am creating several UNION queries to eventually combine data from 3 different company databases.

One of the current queries looks like this:

SELECT     DocEntry, DocNum, DocType, CANCELED, DocStatus, DocDate, CardCode, CardName, NumAtCard, DocTotal, GrosProfit, Ref1, VatSumSy, DiscSumSy, TaxDate,
                      ShipToCode, OwnerCode, SlpCode
FROM         OINV
UNION ALL
SELECT     DocEntry, DocNum, DocType, CANCELED, DocStatus, DocDate, CardCode, CardName, NumAtCard, DocTotal, GrosProfit, Ref1, VatSumSy, DiscSumSy, TaxDate,
                      ShipToCode, OwnerCode, SlpCode
                     
FROM ORIN      

I want to add the database name or the company name to all queries so I can later combine them or pull the data from each company  separately.

If I can't get the database name, a column with a text field for each would be fine.
I just need to add it to every query. ie: "ABC" "XYZ" or "MMM"

How can I do this?
0
Comment
Question by:actsoft
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39706982
Perhaps I'm over-simplifying it but...

SELECT     'OINV' AS Source, DocEntry, DocNum, DocType, CANCELED, DocStatus, DocDate, CardCode, CardName, NumAtCard, DocTotal, GrosProfit, Ref1, VatSumSy, DiscSumSy, TaxDate,
                      ShipToCode, OwnerCode, SlpCode
FROM         OINV
UNION ALL
SELECT     'ORIN', DocEntry, DocNum, DocType, CANCELED, DocStatus, DocDate, CardCode, CardName, NumAtCard, DocTotal, GrosProfit, Ref1, VatSumSy, DiscSumSy, TaxDate,
                      ShipToCode, OwnerCode, SlpCode
                     
FROM ORIN
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39707027
That's exactly what I understood from the question too BriCrowe!
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 39707058
--- What's the current database name?
SELECT db_name()

Also, where you have OINV and ORIN, usually if you're sporting a cross-database query the label is handled above, and you have to spell out database name.schema name.object name (or just database name..object name if it's the default schema), like this ...

SELECT 'OINV' AS Source, DocEntry, DocNum, DocType, CANCELED, DocStatus, DocDate, CardCode, CardName, NumAtCard, DocTotal, GrosProfit, Ref1, VatSumSy, DiscSumSy, TaxDate, ShipToCode, OwnerCode, SlpCode
FROM OINV..SomeTable
UNION ALL
SELECT  'ORIN', DocEntry, DocNum, DocType, CANCELED, DocStatus, DocDate, CardCode, CardName, NumAtCard, DocTotal, GrosProfit, Ref1, VatSumSy, DiscSumSy, TaxDate, 
ShipToCode, OwnerCode, SlpCode
FROM ORIN..SomeTable

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:actsoft
ID: 39707082
The SELECT db_name(), worked perfectly. Thanks
0
 

Author Closing Comment

by:actsoft
ID: 39708809
worked perfectly, thanks
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39708960
Thanks for the grade, although I'm guessing BriCrowe's comment may have helped too.
Good luck with your project.  

Jim
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question