Solved

SQL Select UNION add database name

Posted on 2013-12-09
6
395 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
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 65

Accepted Solution

by:
Jim Horn earned 500 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 65

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

919 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now