Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help for a Query Expand / Collapse
Author
Message
Posted Tuesday, October 23, 2012 2:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 21, 2014 5:20 AM
Points: 273, Visits: 991
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...
Post #1375914
Posted Tuesday, October 23, 2012 3:02 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 3, 2014 9:25 AM
Points: 895, Visits: 2,432
Simple question have you checked the DATA_STAGE table for duplicated rows?


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1375919
Posted Tuesday, October 23, 2012 3:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 21, 2014 5:20 AM
Points: 273, Visits: 991
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..........
Post #1375924
Posted Tuesday, October 23, 2012 3:30 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 3, 2014 9:25 AM
Points: 895, Visits: 2,432
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
Post #1375931
Posted Tuesday, October 23, 2012 3:40 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 6:53 AM
Points: 2,165, Visits: 1,752
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.
Post #1375935
Posted Tuesday, October 23, 2012 3:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 21, 2014 5:20 AM
Points: 273, Visits: 991

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,
Post #1375937
Posted Tuesday, October 23, 2012 4:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 21, 2014 5:20 AM
Points: 273, Visits: 991
Hi Richard Warr,

Thanks Fo reply,

Can you please help me how to wright that Query using Merge........


Many Thanks...............

Post #1375952
Posted Tuesday, October 23, 2012 4:45 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 3, 2014 9:25 AM
Points: 895, Visits: 2,432
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
Post #1375955
Posted Monday, October 29, 2012 3:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 21, 2014 5:20 AM
Points: 273, Visits: 991
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.
Post #1378169
Posted Monday, October 29, 2012 4:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:06 AM
Points: 83, Visits: 296
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

Post #1378191
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse