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


DETECT ROWS CHANGED IN A TABLE to transfer in a datawarehouse


DETECT ROWS CHANGED IN A TABLE to transfer in a datawarehouse

Author
Message
registrazioni 75612
registrazioni 75612
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 18
Hi all.
Every day, I need to transfer data from SQLServer in csv format.
I need to identify wich are the rows changed since 'last time'.

Can you suggest me the best way?
trigger?
timestamp compare ?

Thank you
max
MysteryJimbo
MysteryJimbo
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5269 Visits: 15346
I've used a few in the past. CHECKSUM when I only had to monitor modifications to a few columns. Timestamp is another method.

A trigger could work but I'm not a fan of adding additional code to monitor data in this way.

What are you trying to achieve?
registrazioni 75612
registrazioni 75612
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 18
(Thank you for your reply)
I've order/details table, with milions rows and thousand of new records every day.
I need to transfer every time orders/details changed to another database for analisys.
They ask me a csv format.
My problem is not create csv file, my problem is how to quicly identify changes.

thank you.
Max
MysteryJimbo
MysteryJimbo
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5269 Visits: 15346
Is the tables primary key an identity?
Do you have an inserted date column with associated index?
Can the table be partitioned on a daily basis?

Both having/adding an identity column and an inserted date column would be quite quick at identifying the inserted columns.
registrazioni 75612
registrazioni 75612
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 18
Thank you so much.
I've identity columns but,
I've have to intercept updated datas.

ty
max
Steve Jones
Steve Jones
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148126 Visits: 19444
There's no good way to detect changes in a CSV. They're not built for this, which is why they don't make good databases.

What I'd suggest is that you import the CSV into a staging table, index it, and then look for changes. There are ways to do this, perhaps with data in the table, using checksums as noted, or perhaps joining to your imported data.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19897 Visits: 7415
Change Tracking should handle your situation easily and completely. IIRC, CT requires that the table have a PRIMARY KEY defined.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10428 Visits: 5157
registrazioni 75612 (11/21/2013)
Thank you so much.
I've identity columns but,
I've have to intercept updated datas.

ty
max


CDC (change data capture) is aimed for that. I think it's the best option for you.

Regards,
IgorMi

Igor Micev,
My blog: www.igormicev.com
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19897 Visits: 7415
CDC could do it too, of course, but it has more overhead and is available only in Enterprise Edition; it's overkill for what you've described you need.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
registrazioni 75612
registrazioni 75612
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 18
Ty.
Please, what is IIRC please?
just a link to a doc site.

Ty
Max
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