1-Table & 2-Table data migrate

  • Hi,

     

    I tend to copy data from Table1 to Table2.

    Table1 will always being refresh & truncate, because it will subsequent load new record into it.

     

    Table2 will be permanent main table.

     

    As i know there is a way:-

    1. Merge
    2.  Cursor
    3. For Loop

    Is there any good way to achieve that?

     

     

  • So, as far as I understood you need to insert records into Table2 from Table1 that do not exist in Table2. Then you can use following approach:

    INSERT INTO table2
    SELECT col1, col2 FROM table1 t1
    WHERE NOT EXISTS (
    SELECT t1.col1, t1.col2
    INTERSECT
    SELECT t2.col1, t2.col2
    FROM table2 t2
    )
  • The MERGE command is helpful:

    "Runs insert, update, or delete operations on a target table from the results of a join with a source table. For example, synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table."

    https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15

  • I would not recommend using MERGE. First of all, how would you compare that there is diff? Join by all columns? Then what about nulls?

    Moreover MERGE functionality is buggy... Please read following article: https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/

    To addition to my previous comment, if in that table the problem is not only missing records, but the data can be different, then I suggest to run following update first:

    UPDATE t1
    SET t1.col1 = t2.col1, t1.col2 = t2.col2,... -- list all columns there
    FROM table1 t1
    JOIN table2 t2 ON t2.PK = t1.PK

    And then run query from my previous comment

  • LLSQL42 wrote:

    Hi,

    I tend to copy data from Table1 to Table2.

    Table1 will always being refresh & truncate, because it will subsequent load new record into it.

    Table2 will be permanent main table.

    As i know there is a way:-

     

      <li style="list-style-type: none;">

    1. Merge

     

      <li style="list-style-type: none;">

    1.  Cursor

     

      <li style="list-style-type: none;">

    1. For Loop

     

    Is there any good way to achieve that?

    There's no reason to have to copy the data.  Create a synonym that points to the "active" table and another synonym that points to the table that you'll truncate and load.  Once the load is complete, you can just repoint both synonyms to the other table.  That way, your total downtime is measured in milliseconds.

    Of course, the "active" synonym would be named the same as your original table so that you wouldn't need to make any code changes anywhere.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • The use of synonyms is a great suggestion.

    I had not used synonyms until recently.  I am using them to mask table name variations in SAP HANA DB.  This seems like another good use for them.

  • INSERT INTO table2
    SELECT col1, col2 FROM table1 t1
    EXCEPT
    SELECT col1, col2 FROM table2 t2
  • This was removed by the editor as SPAM

Viewing 8 posts - 1 through 8 (of 8 total)

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