DETECT ROWS CHANGED IN A TABLE to transfer in a datawarehouse

Question

DETECT ROWS CHANGED IN A TABLE to transfer in a datawarehouse

registrazioni 75612

SSC Enthusiast

Points: 154
More actions
November 21, 2013 at 7:37 am

#300686

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 1

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 SSC Enthusiast Points: 154 More actions · Answer 2

(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 SSC-Insane Points: 24203 More actions · Answer 3

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 SSC Enthusiast Points: 154 More actions · Answer 4

Thank you so much.

I've identity columns but,

I've have to intercept updated datas.

ty

max

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 5

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.

ScottPletcher SSC Guru Points: 100946 More actions · Answer 6

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) A socialist is someone who will give you the shirt off *someone else's* back.

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 7

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 SSC Guru Points: 100946 More actions · Answer 8

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) A socialist is someone who will give you the shirt off *someone else's* back.

registrazioni 75612 SSC Enthusiast Points: 154 More actions · Answer 9

Ty.

Please, what is IIRC please?

just a link to a doc site.

Ty

Max

ScottPletcher SSC Guru Points: 100946 More actions · Answer 10

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) A socialist is someone who will give you the shirt off *someone else's* back.