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
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2209 Visits: 15344
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
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2209 Visits: 15344
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
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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 (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61915 Visits: 19099
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
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7815 Visits: 7144
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)[size=2]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.[/size]
Igor Micev
Igor Micev
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5860 Visits: 5080
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,
SQL Server developer at Seavus
My blog: www.igormicev.com
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7815 Visits: 7144
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)[size=2]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.[/size]
registrazioni 75612
registrazioni 75612
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

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