Maintain Delete flag in target table

Question

Maintain Delete flag in target table

abhas

SSCarpal Tunnel

Points: 4681
More actions
December 3, 2013 at 11:26 pm

#279579

Hi,
I am pulling records from source table and inserting into target table. this is incremental load. If new records found then i am inserting, if update happens in source then i am updating in target. I am facing issue to maintain delete flag in target. If record is deleted from source then how could i manage in target that this record is deleted from source?
please suggest idea.
Thanks
Abhas.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

If both source and target are on the same server, you could use the TSQL MERGE statement.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 2

Thanks Koen,

But in the target table there is one extra column ISdelete. If record is deleted from source then i need toset value of this column is D. If i use merge then updating is not allowed under if not match

Thnks

Phil Parkin SSC Guru Points: 246977 More actions · Answer 3

abhas (12/4/2013)
Thanks Koen,
But in the target table there is one extra column ISdelete. If record is deleted from source then i need toset value of this column is D. If i use merge then updating is not allowed under if not match
Thnks

... when not matched by source then

update

set IsDelete = 'D'

...

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 4

Thanks Phil,

I am using below query but getting an error

MERGE dbo.tblcustomerSource AS d -- this is source

USING dbo.tblcustomerTarget AS dd --this is target

ON (d.custid = dd.custid )

when not matched by source then

update

set dd.IsDelete = 'D';

error is below

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dd.IsDelete" could not be bound.

Can you please help?

Thanks

Abhas.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

You have to switch the source and the target in your query.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 6

Yes,Thanks. the query is working fine but its taking time to execute a package. After lookup i am using conitionalsplit transformation and comparing sourceid with targetid and fetching records but its not working.

example i am using below expression.

isnull(sourceid) && !isnull(targetid) but its not working.

Thanks for your help.

Phil Parkin SSC Guru Points: 246977 More actions · Answer 7

abhas (12/4/2013)
Yes,Thanks. the query is working fine but its taking time to execute a package. After lookup i am using conitionalsplit transformation and comparing sourceid with targetid and fetching records but its not working.
example i am using below expression.
isnull(sourceid) && !isnull(targetid) but its not working.
Thanks for your help.

Awesome. Not only have you used my personal favourite ("It's not working"), but neither have you asked a question.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 8

Hi Phil,

by comparing source and target hoe to update the target isdelete flag if record is deleted from source?

Thanks

abhas

Umer Akram SSC-Insane Points: 22209 More actions · Answer 9

can you use this

Update dd

set dd.IsDelete = 'D'

From dbo.tblcustomerTarget dd

Left join dbo.tblcustomerSource d on d.custid = dd.custid

Where d.custid is null

Phil Parkin SSC Guru Points: 246977 More actions · Answer 10

abhas (12/4/2013)
Hi Phil,
by comparing source and target hoe to update the target isdelete flag if record is deleted from source?
Thanks
abhas

You'd already written 99% of the required query:

MERGE dbo.tblcustomerTarget AS dd --this is target

USING dbo.tblcustomerSource AS d -- this is source

ON (d.custid = dd.custid )

when not matched by source then

update

set dd.IsDelete = 'D';

Note that this assumes that dbo.tblcustomerSource contains all of your customer source rows.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 11

Thanks twin ,

I am using the same logic and it working fine. but my source is linked sqlserver. The above query is working fine if source and target is the same server.

could you help me same for linked server?

Thanks

Abhas.

Umer Akram SSC-Insane Points: 22209 More actions · Answer 12

Add the Destination Server through "sp_addlinkedserver" For more detail refer to the following http://technet.microsoft.com/en-us/library/ms190479.aspx

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 13

Koen Verbeeck (12/4/2013)
If both source and target are on the same server, you could use the TSQL MERGE statement.

Unless the table is absolutely huge, why not just copy the whole table to a staging table on the target and do a cascade-rename to put it into service? Depending on index and FK requirements, it's a bit more complicated but any contention is relegated to the milliseconds it takes for sp_Rename to execute.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 14

Thanks twin,

but again interrupting.

linked server is already defined and i am able to select records using the linked server. Facing issue with merge statement only.

please help.

thank you