November 21, 2013 at 7:37 am
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
November 21, 2013 at 9:06 am
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?
November 21, 2013 at 9:42 am
(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
November 21, 2013 at 9:45 am
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.
November 21, 2013 at 10:20 am
Thank you so much.
I've identity columns but,
I've have to intercept updated datas.
ty
max
November 21, 2013 at 10:56 am
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.
November 21, 2013 at 11:16 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 21, 2013 at 3:05 pm
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
November 21, 2013 at 7:33 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 22, 2013 at 12:24 am
Ty.
Please, what is IIRC please?
just a link to a doc site.
Ty
Max
November 22, 2013 at 8:04 am
registrazioni 75612 (11/22/2013)
Ty.Please, what is IIRC please?
just a link to a doc site.
Ty
Max
IIRC == "If I recall correctly" == hedge in case I'm wrong 🙂
For more info on Change Tracking or Change Data Capture, see "Books Online".
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply