Find Missing Records.(urgent) - 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 2000 Forums

Transact-SQL (2000)

Find Missing Records.(urgent)

Author

Topic

ragh
Starting Member

34 Posts

Posted - 2005-12-23 : 06:49:17

Hi there
1. I have a customer table(ex: cust_id, Custname)
2. I have a tempcust Table(ex: cust_id)
3. I have a delCust Table(ex: cust_id)

A. I will bulk insert into tempCust table.

B. Then I will insert into delcust table comparing with cust table, so that the matching cust_id in customer table and temptable will be inserted in delcust table.

C. now i want to find the records that are missed in comparing with delcust,customer, and tempcust table. causes may be not come in textfile, or maybe other reasons.

Can anyone please help me.
regards

Ragh

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-23 : 06:59:59

Hi,
To get all the records not existing in delcust and tempcust
select * from customer where cust_id not in(select cust_id from delcust union select cust_id from tempcust)

ragh
Starting Member

34 Posts

Posted - 2005-12-23 : 07:11:29

Thanks sallu, its working, but here is some tips it must give u some idea.

Customer Table: 1598 Customer from city = 'ADDS'
Text File: 5659 Lines
TempCust table: 5659 Rows (From Bulk Insert TextFile)
DelCust table: 1098 Rows (after a following query is fired).
INSERT INTO delCust
SELECT a.cust_id,a.city FROM tempCust a, customers b
WHERE a.cust_id = b.cust_id AND a.city = b.city

Why such difference? any problem in query?

Ragh

mr_mist
Grunnio

1870 Posts

Posted - 2005-12-23 : 07:47:13

What are you trying to do?

-------
Moo. :)

ragh
Starting Member

34 Posts

Posted - 2005-12-25 : 00:50:16

Details are below:
===================
I have 4 tables totally
1. Customers table
2. TempCust table (sr_no is int type IDENTITY Column NOT NULL)
3. delCust table (sr_no is int type and NOT NULL)
4. currentCust table. ( sr_no is int type IDENTITY Column NOT NULL)

Process:
====================
1. I will bulk insert a textfile into TempCust table.
2. I will cross check tempCust with Customer Table for cust_id, if matches then i will Insert matching rows in delCust Table.
3. From delCust i must insert it into currentCust.

Problems:
====================
A. Sometimes i'm getting duplicate records in currentcust table, i must not get actually.
B. Sometimes i wont find the records that are in Customer Table and not in currentCust table.

Queries used:
====================
1. BULK INSERT tempCust from "c:\FileOut\APMCYAbl.txt" with ( FieldTerminator = ';' , ROWTERMINATOR = ';')

2. insert into delCust select a.sr_no,a.cust_type,a.cust_id,a.branchcode from tempCust a, Customers b where a.cust_id = b.Cust_id and a.cust_type = b.cust_type and a.branchcode = b.branchcode"

3. insert into currentCust select cust_type,cust_id,branchcode flag from delCust

4.delete from CurrentCust where sr_no in(select distinct a.sr_no from CurrentCust a, delCust b where a.branchcode=b.branchcode and a.cust_id = b.cust_id and a.cust_type= b.cust_type and b.flag = '1' and a.flag = '0'

Any suggestions please help me.

Ragh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources