Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
kond.mohan
Posting Yak Master
213 Posts |
Posted - 2013-03-04 : 07:51:25
|
hi i am loading the data from source(oracle) to Destination(sql server).one of my incremental table loaded partially(count is mismatch with source data) actully TABLE Count is 9525653.i have loaded full dump of that table into another database.i want to know the missing records of my exact table .i have used by this query.select acid+bank_id from SSISLOGS..abc except select acid+bank_id from DWH_STAGING..abc.but above query is taking more than 60 min without output. if any otherway pls suggest |
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-03-04 : 08:10:33
|
SELECT A.* FROM SSISLOGS..abc A --This table should behaving higher countLeft Join DWH_STAGING..abc B on A.acid=B.acid AND A.bank_id =B.bank_id WHERE B.acid is nullCheersMIK |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-05 : 00:00:26
|
ot use not existsselect acid+bank_id from SSISLOGS..abc awhere NOT EXISTS (SELECT 1 FROM DWH_STAGING..abc WHERE acid+bank_id = a.acid+a.bank_id )------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
kond.mohan
Posting Yak Master
213 Posts |
Posted - 2013-03-06 : 10:10:46
|
hi previous query is executed finein the same wayselect SOL_ID+CUST_ID+TDS_CERT_NUM+CONVERT(VARCHAR,TRAN_DATE)+BANK_ID from SSISLOGS..tds_new awhere NOT EXISTS (SELECT 1 FROM DWH_STAGING..tds WHERE SOL_ID+CUST_ID+TDS_CERT_NUM+CONVERT(VARCHAR,TRAN_DATE)+BANK_ID = a.sol_id+a.cust_id+a.tds_cert_num+a.CONVERT(VARCHAR,TRAN_DATE)+a.bank_id)below query is showing errorIncorrect syntax near the keyword 'CONVERT' |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-06 : 10:36:45
|
The alias should be on the column (i.e., inside the convert function). Also, when you use VARCHAR, always specify a lenght.SELECT SOL_ID + CUST_ID + TDS_CERT_NUM + CONVERT(VARCHAR(512), TRAN_DATE) + BANK_IDFROM SSISLOGS..tds_new aWHERE NOT EXISTS ( SELECT 1 FROM DWH_STAGING..tds WHERE SOL_ID + CUST_ID + TDS_CERT_NUM + CONVERT(VARCHAR(512), TRAN_DATE) + BANK_ID = a.sol_id + a.cust_id + a.tds_cert_num + CONVERT(VARCHAR(512), a.TRAN_DATE) + a.bank_id ) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-06 : 12:41:49
|
also one more thing comparing concatenated column values is not same as comparing individual columns separatelyThere can be a small chance of concatenated value of columns matching though individuals values wont actually matchHopefully you're looking at former scenario.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
kond.mohan
Posting Yak Master
213 Posts |
Posted - 2013-03-08 : 10:12:25
|
hiusing same kind of key fields(SOL_ID + CUST_ID + TDS_CERT_NUM + CONVERT(VARCHAR(512), TRAN_DATE) + BANK_ID) is there any chance to load the duplicates in such kind(key fields like above manner) of scenario.into the staging area.if yes then how can we identify the duplicates?my staging is showing duplicates. i have fired the below querybut i did not get any kind of results.select SOL_ID + CUST_ID + TDS_CERT_NUM + CONVERT(VARCHAR(512), TRAN_DATE) + BANK_ID)from abc group by SOL_ID + CUST_ID + TDS_CERT_NUM + CONVERT(VARCHAR(512), TRAN_DATE) + BANK_ID)having count(*) >1 |
|
|
|
|
|
|
|