Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
Hi,
I need a query to get the data from two tables which are in two different databases.
I have the columns like RegId,GaurId,transID,payamount,status,paymethod in table A in database A and having the coloumns like eventid,CAC,Amount,paystatus in table B in Database B.How to union these tables.Can anyone help me regarding this?
Posted 19-Mar-13 5:41am

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hey if the two databases are on the same server its just simply dong like so
 
SELECT col1, col2, col3 FROM Database1.Schema1.Table1
UNION 
SELECT col1, col2, col3 FROM Database2.Schema2.Table2
 
And remember to union you must seklect the same numbetr of columns from both and the columns must be the same datatype.
  Permalink  
v2
Comments
priya9826 at 19-Mar-13 10:51am
   
But the some of the coloumns are different.It throws the error for me
priya9826 at 19-Mar-13 10:52am
   
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

This is the error which has thrown for me.
Mickt1985 at 19-Mar-13 11:14am
   
You cannot do a union if the columns are of different datatypes.
priya9826 at 19-Mar-13 11:26am
   
whats the resolution for this?then how can I extract the data from 2 tables
phil.o at 19-Mar-13 11:55am
   
Make two distinct queries.
Mickt1985 at 19-Mar-13 11:41am
   
Your tables have no relationship, can you please tell me what you are trying to acheive from doing this?
priya9826 at 19-Mar-13 12:03pm
   
select
C.PaymentID
,C.PayDate
,C.PayFrom
,C.MonerisMsg
,C.GuardianId
,C.RegId
,C.TransId
,C.Amount
,C.PayStatus
,C.CardAuthorizationCode
--,(Sum(C.Amount)) as TotalPaidAmount
from
(select
A.PaymentID
,A.PayDate
,A.PayFrom
,A.MonerisMsg
,Null as GuardianId
,Null as RegId
,Null as TransId
,A.Amount
,A.PayStatus
,A.CardAuthorizationCode
from DCEOnlinePaymentTest.dbo.tblPayment A
union all
select
B.PaymentId
,B.PayDate
,B.PayFrom
,B.MonerisMsg
,B.GuardianId
,B.RegId
,B.TransId
,B.PayAmount as Amount
,B.Status as PayStatus
,Null as CardAuthorizationCode
from LakesideLabSQLTest.dbo.tblUserPayment B) as C
where C.PayDate>'2012-12-12' and C.PayDate
priya9826 at 19-Mar-13 12:13pm
   
I am able to union the 2 tables but I am not able to get the total amount which is Sum(C.Amount).How to get that?
Mickt1985 at 19-Mar-13 12:18pm
   
Looks like you are trying to sum the wrong thing Replace Sum(C.Amount) as TotalPaidAmount, with (Sum(Amount)) as TotalPaidAmount
priya9826 at 19-Mar-13 12:37pm
   
Actually I need to get the sum(amount) where paystatus='approved'.Its not working for this in this scenario: (select Sum(C.Amount) from C
where UPPER(C.PayStatus)='Approved' and C.PayDate>'2012-12-12' and C.PayDate
Mickt1985 at 19-Mar-13 13:04pm
   
your converting C.Paystatus to UPPERCASE and then trying to compare it to Lowercase letters?
priya9826 at 19-Mar-13 14:45pm
   
Its a typo mistake.
Mickt1985 at 19-Mar-13 18:59pm
   
;WITH cte
AS (SELECT A.paymentid,
A.paydate,
A.payfrom,
A.monerismsg,
NULL AS GuardianId,
NULL AS RegId,
NULL AS TransId,
A.amount,
A.paystatus,
A.cardauthorizationcode
FROM dceonlinepaymenttest.dbo.tblpayment A
UNION ALL
SELECT B.paymentid,
B.paydate,
B.payfrom,
B.monerismsg,
B.guardianid,
B.regid,
B.transid,
B.payamount AS Amount,
B.status AS PayStatus,
NULL AS CardAuthorizationCode
FROM lakesidelabsqltest.dbo.tbluserpayment B)
SELECT cte.paymentid,
cte.paydate,
cte.payfrom,
cte.monerismsg,
cte.guardianid,
cte.regid,
cte.transid,
cte.amount,
cte.paystatus,
cte.cardauthorizationcode,
Sum(cte.amount) AS TotalPaidAmount
FROM cte
GROUP BY cte.paymentid,
cte.paydate,
cte.payfrom,
cte.monerismsg,
cte.guardianid,
cte.regid,
cte.transid,
cte.amount,
cte.paystatus,
cte.cardauthorizationcode
priya9826 at 26-Mar-13 10:45am
   
Thanks a lot!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 245
1 Kamal Rocks 184
2 Sergey Alexandrovich Kryukov 175
3 BillWoodruff 173
4 PIEBALDconsult 160
0 OriginalGriff 5,655
1 DamithSL 4,506
2 Maciej Los 3,997
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,180


Advertise | Privacy | Mobile
Web04 | 2.8.141216.1 | Last Updated 19 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100