October 23, 2012 at 2:55 am
HI,
We a have 2 tables.I need to insert values form DAT_STAGE table to DAT_DES with out duplicate records.
I have used below query.
insert into DAT_DES
(lvl_nme, ds_nme, ds_size, create_dt, rating_dt)
(select LTRIM(REPLACE(DAT_STAGE.lvl_nme, 'D', '')), ds_nme, ds_size, create_dt, substring(ds_nme, 3, 8)
from DATA_STAGE
where DAT_STAGE.ds_nme + LTRIM(REPLACE(DAT_STAGE.lvl_nme, 'D', '')) NOT in
(select ds_nme + str(lvl_nme) from DATA_DES))
But it is inserting duplicate values.can any one help me how to insert values with out inserting the duplicate recored.
plz help me to modify the above query...
October 23, 2012 at 3:02 am
Simple question have you checked the DATA_STAGE table for duplicated rows?
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 23, 2012 at 3:20 am
we have an ssis packa created in such a way that
Data will loaded into the DAT_STAGE from Flat file and then we need to insert that values from Dat_stage to Dat_DES.
Daily this SSIS job will run and data will in to the table.
we have created a step in the JOb in such way From DAT_STAGE values should insert into the DAT_DES with out duplicate values with the above query which mention.
But it is not working........
can any one give me an idea....
Thank you very much for reply......
Many thanks..........
October 23, 2012 at 3:30 am
I cant see anything wrong with the query, thats why I asked if there were duplicated records in the staging table.
If you run
SELECT
lvl_nme
,ds_nme
,count(*)
From DAT_STAGE
Group by
lvl_nme
,ds_nme
Having count(*)>1
Do you get any duplicates?
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 23, 2012 at 3:40 am
If using SQL2008 you can also use a MERGE instead of the INSERT...WHERE NOT IN structure. This should be much more robust and considerably faster.
October 23, 2012 at 3:46 am
There are no duplicate values in the staging table ....
SSIS job is created in such a way that after inserting the Values to DAT_DES form DAT_STAGE we will delete the data present on the DAT_STAGE table.....
Next day when the ssis job runs the fresh data will load into the DAT_Stage....from Flat file...
Some times this data will contain the same values...i.e values that are present on DAT_DES.......
So we need to insert only those values which does not contain on DAT_DES.....
Many THanks,
October 23, 2012 at 4:39 am
Hi Richard Warr,
Thanks Fo reply,
Can you please help me how to wright that Query using Merge........
Many Thanks...............
October 23, 2012 at 4:45 am
Ok, If you state there are no duplicates in the DAT_STAGE then thats fine, I'm always suspicious of data loaded from files as they can easily have duplicates in them, and neither of the solutions below will prevent duplicates occuring if there are duplicated rows in the source file.
There are a couple of options, using the merge statement is one,
MERGE INTO DAT_DES as Target
USING DAT_STAGE as Source
ON Target.ds_nme=Source.ds_nme
and Targer.lvl_nme = LTRIM(REPLACE(Source.lvl_nme, 'D', ''))
When NOT MATCHED BY TARGET THEN
INSERT (lvl_nme, ds_nme, ds_size, create_dt, rating_dt)
VALUES LTRIM(REPLACE(Source.lvl_nme, 'D', '')), Source.ds_nme, Source.ds_size, Source.create_dt, substring(Source.ds_nme, 3, 8));
The other is to use a NOT EXISTS, Something like
insert into DAT_DES
(lvl_nme, ds_nme, ds_size, create_dt, rating_dt)
SELECT LTRIM(REPLACE(DAT_STAGE.lvl_nme, 'D', '')), ds_nme, ds_size, create_dt, substring(ds_nme, 3, 8)
from DAT_STAGE s
WHERE NOT EXISTS
(SELECT 1
FROM DAT_DES d
WHERE d.ds_nme=s.ds_nme
AND d.lvl_nme=LTRIM(REPLACE(s.lvl_nme, 'D', '')))
I'm not sure how this will perform as I've seen strange query performances when modifying data in a NOT EXISTS where clause.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 29, 2012 at 3:46 am
THanks you so much for helping me with the Query...
we want to delete the duplcate record from a table (DAT_DES--which i mentinon above).
Can you please help me how to wright the query to delete the duplcate record on the table " DAT_DES"
using merge.
Many thanks,
Vijay.
October 29, 2012 at 4:58 am
If you using merge solution given by "Jason-299789 " then duplicate data will not going to come
and if you want to delete duplicate record from same table
then use simple row_number,or using distinct
you can delete data
like
delete from
(
select a,row_number() over (partition by a order by a) as srno,b
from @temptable
) c
where srno>1
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply