Sql Server / Rdbms Experts - Need Your Inputs

[giri3005]

What if you create the same table , copy the data from main table @ End of Day .

Next Day , Then do A Minus B  Union All B Minus A, you should get the delta data you want.

 

 

This is a round about work but see whether this helps( You might think of extra space ).

 

 

[mettastar]

What if you create the same table , copy the data from main table @ End of Day .

Next Day , Then do A Minus B  Union All B Minus A, you should get the delta data you want.

 

 

This is a round about work but see whether this helps( You might think of extra space ).

idi optimal antavaa? and also i dont have control over source system.. Triggers ante request cheyagaluguthanu but inko replica create cheyalante they wont do emo

[bowne1980]

Brother,

 in Sql server  you have statement called Merge,Its ideal for 

doing Deltas.

 

Ex:

 

Table A: ID,NAME   (source)

Table B: ID,Name    (Target)

 

your Mrge Statemtn would be like this.

 

Merge Table B as Target

Using Table A as Source

on Taget.ID=Source.ID

 

When Matched AND

 

Source.NAME<>Target.NAME

 

Then Update Targe.Name=Source.Name

 

When Not Matched Then 

Insert(ID,NAME)

Values(Source.ID,Source.NAME)

 

When Not Matched by Source Then Delete;

 

Note: Your Source can be a table or Query.

 

[mtkr]

Brother,
in Sql server you have statement called Merge,Its ideal for
doing Deltas.

Ex:

Table A: ID,NAME (source)
Table B: ID,Name (Target)

your Mrge Statemtn would be like this.

Merge Table B as Target
Using Table A as Source
on Taget.ID=Source.ID

When Matched AND

Source.NAME<>Target.NAME

Then Update Targe.Name=Source.Name

When Not Matched Then
Insert(ID,NAME)
Values(Source.ID,Source.NAME)

When Not Matched by Source Then Delete;

Note: Your Source can be a table or Query.

i guess he wants to pull deltas aftr data had been loaded( on existing data tbl)

Star bayya.. na assumption crrt e naa!!! nuv deltas existing tbl data nundi pull cheyyali gaa!!!

r while loading data frm tbl to othr?? if one tbl to othr ante az bowne bayya said use MERGE..

[mettastar]

bro na target table kadhu :D.. Hadoop/HDFS loki loading data ni

[mettastar]

i guess he wants to pull deltas aftr data had been loaded( on existing data tbl)

Star bayya.. na assumption crrt e naa!!! nuv deltas existing tbl data nundi pull cheyyali gaa!!!

r while loading data frm tbl to othr?? if one tbl to othr ante az bowne bayya said use MERGE..

yep.. i cant compare source and target.. 

[bowne1980]

Sorry brother,Naaku hadoop idea ledu!

[mtkr]

So nuv sql server tbl meeda query or somethng apply chesi only deltas pull cheyyalii.. anthe gaa!!!
n u dont have complete access on tht tbl... like to change its structure or to create replica of it!!!

[giri3005]

idi optimal antavaa? and also i dont have control over source system.. Triggers ante request cheyagaluguthanu but inko replica create cheyalante they wont do emo

 

You are left with minimal options as you dont have an audit column

 

1. Triggers ( this is not optimal as you load/change large volumes of data)

2. Read logs ( Sorry but cant say how this works in your case)

3. To create a replica of the table and compare ( my suggestion).

( you have no performance problems and you dont rely on any other sources)

 

This will be challenging as source is not in your control.

[mtkr]

Bayya "change data capture" and "change tracking" ani concepts unnaii.. time unte chudu...
but u need to apply some systm sps on those tbls..

[mettastar]

So nuv sql server tbl meeda query or somethng apply chesi only deltas pull cheyyalii.. anthe gaa!!!
n u dont have complete access on tht tbl... like to change its structure or to create replica of it!!!

yep source system ki readonly access undhi prasthutham.. 

approach decide aithe vaallani request cheyali.. either create a trigger and load the change info in a new table or get using transaction log data

[pachimirchi]

If you can have someone make changes for you on the data source system then try any of the  approaches

 

1.journaling/ change tracking/ CDC source meeda use chesi we can pull deltas..

2. updated_date ani create a column and default it to sys date/getdate(); 

 

 

target hadoop kabatti, I'm confident file compare function vuntadi in Java..

so, spit the source data into a file and target data into another file and use compare methods to get deltas..

 

if using pig, you can use left join to get deltas as well..

 

 

 

 

 

[mettastar]

If you can have someone make changes for you on the data source system then try any of the approaches

1.journaling/ change tracking/ CDC source meeda use chesi we can pull deltas..
2. updated_date ani create a column and default it to sys date/getdate();


target hadoop kabatti, I'm confident file compare function vuntadi in Java..
so, spit the source data into a file and target data into another file and use compare methods to get deltas..

if using pig, you can use left join to get deltas as well..

data Motham pull cheyakudadhu ane delta option chusthunna so file comparison ruled out.
Source table ni alter cheyalem baa like adding a col.

Only option CDC enable chesi try cheyali Mari

[mettastar]

If you can have someone make changes for you on the data source system then try any of the approaches

1.journaling/ change tracking/ CDC source meeda use chesi we can pull deltas..
2. updated_date ani create a column and default it to sys date/getdate();


target hadoop kabatti, I'm confident file compare function vuntadi in Java..
so, spit the source data into a file and target data into another file and use compare methods to get deltas..

if using pig, you can use left join to get deltas as well..

data Motham pull cheyakudadhu ane delta option chusthunna so file comparison ruled out.
Source table ni alter cheyalem baa like adding a col.

Only option CDC enable chesi try cheyali Mari

[mettastar]

Date field okati add chestham annaru source system vaalluu... triggers kuda oppukoledu performance issue avuthadhi ani..

 

LastModifiedDate unte naa process kuda easy aipothadi..

 

Thanks Guys