SQL Select UNION add database name

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?
actsoftAsked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
--- 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
 
Brian CroweDatabase AdministratorCommented:
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
 
Angelp1ayCommented:
That's exactly what I understood from the question too BriCrowe!
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
actsoftAuthor Commented:
The SELECT db_name(), worked perfectly. Thanks
0
 
actsoftAuthor Commented:
worked perfectly, thanks
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade, although I'm guessing BriCrowe's comment may have helped too.
Good luck with your project.  

Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.