How can we help you today? How can we help you today?

Force newly updated rows from a source to a target database?

I've been using SQL Data Compare for years but I can't figure out a way to do this simple task...

I'm updating rows in my source database and it has a column called "DateUpdated". This field will get set to today's date when new data is written to the row, either by insert or update.

So for SQL Data Compare, I only want to compare the rows where "DateUpdated" is >= (today). The key from the source and destination database should match and there would be an update (DateUpdated is not part of the key.). If there is no match, insert it.

Here's how I've tried to set it up...

1. In the Tables and Views dialog, I've set the Comparison Key for the table to be the key that matches the rows between the source and target.
2. For the WHERE clause, I've tried two things: " DateUpdated >= '2024/12/3' " for both tables, or " DateUpdated >= '2024/12/3' " for just the source table.
3. If also tried to remove some of the Columns for Comparison, but if I remove them, the updates for these columns don't get deployed.

I feel like I'm missing something basic. Anyone have some advice or is this just not possible without having to compare a much larger subset of rows?

pclaar
0

Comments

5 comments

Sort by
  • pclaar
    Update: I went to my friend ChatGPT and it appears that SQL Data Compare does not support this scenario, but there is a workaround: https://chatgpt.com/share/674fabd3-b770-8007-9682-cc00a5bc21e4

    If I set the WHERE clause in the source database only, I get the result I want...



    ...if I uncheck the 81819 records. But this is hugely inefficient since I have to read each and every row from the target database.
    pclaar
    0
  • JoshH
    Unfortunately, you're correct. There isn't currently a way to do this in Data Compare more efficiently. You can put in a feature request here if you'd like.
    JoshH
    0
  • pclaar
    Thanks for confirming.
    pclaar
    0
  • GPTDeutsch

    Interessante Frage! Ich hatte ein ähnliches Problem mit SQL Data Compare und habe damals mit Chat GPT Deutsch darüber gesprochen. Was mir geholfen hat, war die Kombination eines gefilterten Views in der Quell-Datenbank (nur DateUpdated >= GETDATE()) und die Auswahl aller relevanten Vergleichsspalten (auch wenn sie nicht als Schlüssel dienen). Wichtig: Entferne keine Spalten, die du aktualisieren willst – sonst werden sie bei der Synchronisation ignoriert. Ein zusätzlicher Tipp von ChatGPT war, ein temporäres Synchronisations-Flag zu verwenden, um genau zu steuern, welche Zeilen berücksichtigt werden sollen. Vielleicht hilft dir das weiter?

    GPTDeutsch
    0
  • flokaye

    Похожую задачу я недавно обсуждал с Чат GPT, и он помог мне найти рабочий подход. Главное — использовать корректное условие в WHERE для исходной базы, например: WHERE DateUpdated >= CONVERT(date, GETDATE()). Также важно не исключать столбцы из сравнения, если вы хотите, чтобы изменения в них были применены. В SQL Data Compare можно настроить фильтрацию только для источника и при этом оставить нужные поля для обновления. Рекомендую задать этот вопрос в Чат GPT, он хорошо объясняет шаг за шагом.

    flokaye
    0

Add comment

Please sign in to leave a comment.