SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help for a Query


Need help for a Query

Author
Message
bala2
bala2
Mr or Mrs. 500
Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)

Group: General Forum Members
Points: 525 Visits: 1046
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...
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2115 Visits: 3232
Simple question have you checked the DATA_STAGE table for duplicated rows?

_________________________________________________________________________
SSC Guide to Posting and Best Practices
bala2
bala2
Mr or Mrs. 500
Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)

Group: General Forum Members
Points: 525 Visits: 1046
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..........
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2115 Visits: 3232
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
Richard Warr
Richard Warr
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2802 Visits: 1987
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.

_____________________________________________________________________
MCSA SQL Server 2012
bala2
bala2
Mr or Mrs. 500
Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)

Group: General Forum Members
Points: 525 Visits: 1046
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,
bala2
bala2
Mr or Mrs. 500
Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)

Group: General Forum Members
Points: 525 Visits: 1046
Hi Richard Warr,

Thanks Fo reply,

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


Many Thanks...............
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2115 Visits: 3232
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
bala2
bala2
Mr or Mrs. 500
Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)

Group: General Forum Members
Points: 525 Visits: 1046
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.
BriPan
BriPan
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search