1-Table & 2-Table data migrate

Question

1-Table & 2-Table data migrate

LLSQL42

SSC Enthusiast

Points: 199
More actions
July 10, 2020 at 7:04 am

#3768857
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?

August 15, 2020 at 6:58 am

This was removed by the editor as SPAM

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

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

dkultasev SSC Rookie Points: 34 More actions · Answer 1

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
)

kcecil 47772 Grasshopper Points: 17 More actions · Answer 2

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

dkultasev SSC Rookie Points: 34 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 4

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;">
Merge

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

<li style="list-style-type: none;">
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)

Scott Arendt SSCrazy Eights Points: 8218 More actions · Answer 5

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.

Chris Wooding SSCarpal Tunnel Points: 4554 More actions · Answer 6

INSERT INTO table2
SELECT col1, col2 FROM table1 t1
EXCEPT
SELECT col1, col2 FROM table2 t2