TABLE COUNT Comparison - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

TABLE COUNT Comparison

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 count
Left Join DWH_STAGING..abc B on A.acid=B.acid AND A.bank_id =B.bank_id
WHERE B.acid is null

Cheers
MIK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-05 : 00:00:26

ot use not exists

select acid+bank_id from SSISLOGS..abc a
where NOT EXISTS (SELECT 1 FROM DWH_STAGING..abc WHERE acid+bank_id = a.acid+a.bank_id )

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kond.mohan
Posting Yak Master

213 Posts

Posted - 2013-03-06 : 10:10:46

hi
previous query is executed fine
in the same way
select SOL_ID+CUST_ID+TDS_CERT_NUM+CONVERT(VARCHAR,TRAN_DATE)+BANK_ID from SSISLOGS..tds_new a
where 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 error
Incorrect 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_ID
FROM   SSISLOGS..tds_new a
WHERE  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 separately
There can be a small chance of concatenated value of columns matching though individuals values wont actually match
Hopefully you're looking at former scenario.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kond.mohan
Posting Yak Master

213 Posts

Posted - 2013-03-08 : 10:12:25

hi
using 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 query
but 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources