Insert and updates

getsatish_gk · Post by **getsatish_gk** » Sat Sep 06, 2008 3:19 am

Hi,

I want perform insert if new record or update if exits (don't look for change columns)

Insert: if primary key does't exist in target ( can do with Change capture code=1)

Update some columns if key exists in target (if i use change capture it looks of change column which i don't want to perform)

Please give me hint.

ray.wurlod · Post by **ray.wurlod** » Sat Sep 06, 2008 3:52 am

Two questions:

1. Does the input data contain duplicate key values?

2. What alternative do you plan for detecting change when the row already exists in the target table?

getsatish_gk · Post by **getsatish_gk** » Sat Sep 06, 2008 4:06 am

Two questions:

1. Does the input data contain duplicate key values?
A- looking based on primary keys

2. What alternative do you plan for detecting change when the row already exists in the target table? ..
A-If the row exists with same key then i just want to update few columns in target table, thats it.

ray.wurlod · Post by **ray.wurlod** » Sat Sep 06, 2008 4:44 am

What I was getting at in the first question is what happens if the same key arrives twice in the stream of input data? How do you need to play this?
You can only insert the first of them.

getsatish_gk · Post by **getsatish_gk** » Sat Sep 06, 2008 8:49 pm

Thanks ray,

Just want to do,
- Insert if primary key is not present. else if present
then set some columns to null.

Can it is possible without change capture/difference stage?.

I checked there are no duplicate key coming from source

ray.wurlod · Post by **ray.wurlod** » Sat Sep 06, 2008 11:23 pm

Code: Select all

            RefDS
              |
              |
              V                       ----> insert
source ----> Lookup ----> Transformer
                                      ----> update

Loaded in an earlier job, RefDS contains keys and those columns from the target that you wish to compare. The Lookup stage "lookup failed" rule is Continue. The Transformer stage directs failed lookups to the insert link and detects changes (by direct comparison) where the lookup was successful.

getsatish_gk · Post by **getsatish_gk** » Sun Sep 07, 2008 12:14 am

Thanks for that. Soon i will be bororw a premier membership.
by then let me try to crack ur code.

Well by now... using join stage with left outer join
with left link=source and right link=target(reference)

and output contains is both new and existing records.

How to distinguish between between these records?

ray.wurlod · Post by **ray.wurlod** » Sun Sep 07, 2008 12:53 am

Premium membership is not expensive at less than 30c per day. Don't borrow one, buy one. It will be worth it.

getsatish_gk · Post by **getsatish_gk** » Sun Sep 07, 2008 1:06 am

why i am worried is when ever i post mostly get reply only from you. No other will reply.. this

me

ray.wurlod · Post by **ray.wurlod** » Sun Sep 07, 2008 6:23 am

Maybe no-one else is silly enough to work weekends.

chulett · Post by **chulett** » Sun Sep 07, 2008 8:19 am

Techincally, you've got 20,000+ people who could help you out here, however the vast majority only seem to post when they have a problem. It's a very small group that try to help others, but that's the way it is anywhere, the nature of the beast. And when the Alpha Male steps in, almost everyone else steps back from the kill and lets him have his fill.

Or maybe no-one else is silly enough to work weekends.

getsatish_gk · Post by **getsatish_gk** » Sun Sep 07, 2008 4:55 pm

Yeah, Most of them just waiting for pulling someothers leg

When the things not working, have to work even on nights

ray.wurlod · Post by **ray.wurlod** » Sun Sep 07, 2008 5:02 pm

There are only five premium posters, one of whom is currently sailing (or riding out storms) in the Caribbean. Anyone else with constructive thoughts on any issue is welcome to post, but it's their choice whether they do or not.

Anyway, don't you have an official support provider?