actsoft
asked on
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?
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?
That's exactly what I understood from the question too BriCrowe!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The SELECT db_name(), worked perfectly. Thanks
ASKER
worked perfectly, thanks
Thanks for the grade, although I'm guessing BriCrowe's comment may have helped too.
Good luck with your project.
Jim
Good luck with your project.
Jim
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