June 3, 2009 at 4:28 am
Hi,
i am loading data from text file to the db using a dts package.it involves follwing stages
1)loading data to staging table from textfile
2)matching the records in the staging table with the existing records and updating the staging table "consumerid" column with the consumerid in the Db
3)updating the records in the oroginal table from staging table if consumerID has a value in the staging table after the matching process is done
4)finally inserting all the records into the original table whose consumerids in the staging table are Null
the table structure of the staging table is as follows.
StagingId |ConsumerId |firstname |secondname| nationalIDno |Accountno | address
1 | |a | b | 123456 |12345089 |abcabc
2 | |a | b | 123456 |36780900 |abcabc
i have two original tables one for storing personal info and other for storing Account info
orignal tables structure should be as follows
ConsuemrId firstname secondname nationalIDno
1 a b 123456
ConsumerID AccountNo
1 12345089
1 36780900
from the above tables u can observer the same consumer has two different accounts.
Now the problem is if i am inserting the record for the first time in the database which means i check for the record existance in the original table and the record does not exist which means the "consumerId" in the staging table has null values.and it is inserting the data as two different records and creating two consumerIds how do avoid this duplication problem.
thanks in advance.
June 3, 2009 at 4:43 am
INSERT INTO PersonalInfo (firstname, secondname, nationalDno)
SELECT DISTINCT firstname, secondname, nationalDno
FROM StagingTable S
WHERE NOT EXISTS
(
SELECT *
FROM PersonalInfo P
WHERE S.firstname = P.fistname
AND S.secondname = P.secondname
AND S.nationalDno = P.nationalDno
)
June 4, 2009 at 4:30 am
hi ken,
Thanks for the reply.It worked for me.
February 17, 2012 at 5:25 pm
delete from
tableA
where pid not in
( select pid, CN, mk, te, yr, st, cr, cc from
tableA where st = 812 and te = 1 and YR = 11
group by PID, cn, mk, te, yr, st, CR, cc
having count (*) > 1 )
I am trying to run this query but geting this error message:
Msg 116, Level 16, State 1, Line 7
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
CAn somebody help. Thank you so much.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply