Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

DETECT ROWS CHANGED IN A TABLE to transfer in a datawarehouse Expand / Collapse
Author
Message
Posted Thursday, November 21, 2013 7:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 27, 2013 3:21 AM
Points: 6, 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
Post #1516440
Posted Thursday, November 21, 2013 9:06 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:12 AM
Points: 1,362, Visits: 15,266
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?
Post #1516472
Posted Thursday, November 21, 2013 9:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 27, 2013 3:21 AM
Points: 6, 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
Post #1516489
Posted Thursday, November 21, 2013 9:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:12 AM
Points: 1,362, Visits: 15,266
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.
Post #1516494
Posted Thursday, November 21, 2013 10:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 27, 2013 3:21 AM
Points: 6, Visits: 18
Thank you so much.
I've identity columns but,
I've have to intercept updated datas.

ty
max
Post #1516501
Posted Thursday, November 21, 2013 10:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:13 PM
Points: 33,100, Visits: 15,210
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
Post #1516521
Posted Thursday, November 21, 2013 11:16 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:31 PM
Points: 1,977, Visits: 2,926
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1516528
Posted Thursday, November 21, 2013 3:05 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 28, 2014 4:35 AM
Points: 2,901, Visits: 2,927
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
www.seavus.com
Post #1516599
Posted Thursday, November 21, 2013 7:33 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:31 PM
Points: 1,977, Visits: 2,926
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1516627
Posted Friday, November 22, 2013 12:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 27, 2013 3:21 AM
Points: 6, Visits: 18
Ty.
Please, what is IIRC please?
just a link to a doc site.

Ty
Max
Post #1516661
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse