SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 TABLE COUNT Comparison		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kond.mohan
Posting Yak Master

India
136 Posts
Posted - 03/04/2013 :  07:51:25  Show Profile  Reply with Quote
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
Aged Yak Warrior

Pakistan
823 Posts
Posted - 03/04/2013 :  08:10:33  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 03/05/2013 :  00:00:26  Show Profile  Reply with Quote
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/
Go to Top of Page

kond.mohan
Posting Yak Master

India
136 Posts
Posted - 03/06/2013 :  10:10:46  Show Profile  Reply with Quote
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'
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1501 Posts
Posted - 03/06/2013 :  10:36:45  Show Profile  Reply with Quote
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
       )
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 03/06/2013 :  12:41:49  Show Profile  Reply with Quote
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/
Go to Top of Page

kond.mohan
Posting Yak Master

India
136 Posts
Posted - 03/08/2013 :  10:12:25  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000