CODEWITHSUNDEEP
sqlsql-serverdatabase
Klapsius
Klapsius

Reputation: 3359

update two tables with relationship

I will try explain with examples. I have two SQL databases DB1 and DB2 each database has same tables. Basically everything is related (primary keys is auto increment) but we decide to use DB1 as main data source and update DB2 with information from DB1. I can use merge but what's happens with relations on DB2? I need to remove everything and use DB1 without any relations as plain tables and ten pull information from DB1? I need keep same primary keys as on DB1. How to update DB2 with same auto increment parameters?

example:

TAB1

tab1_primary_key  A   B  C
1                 X1  X2 X3
2                 X5  X1 X2
3                 ...

TAB2

tab_2_primary  tab1_foreign_key      B   D  C
    1           1                    X1  X2 X3
    2           2                    X5  X1 X2
    3           3                    ...

Upvotes: 0

Views: 98

Answers (1)

GabrielVa
GabrielVa

Reputation: 2388

Your Primary key in Tab1 should reflect the same in Tab2 under the foreign Key if everything is setup correctly. Don't worry about the Primary Key in Tab2 as that's just an id. You can remove the foreign_key relationship and still use the value in the merge.

Additionally, You could do something like this for a SQL Merge: 

 MERGE INTO dbo.Tab2  AS target
USING dbo.Tab1 AS source
    ON target.tab1_foreign_key = source.tab1_primary_key
WHEN MATCHED THEN 
    UPDATE SET target.A = source.A, 
 target.B = source.B,
 target.C = source.C,
 target.D = source.D
WHEN NOT MATCHED BY TARGET THEN
 INSERT (A,B,C,D,E)
 VALUES (source.A,source.B,source.C,source.D,source.E );

Upvotes: 1

Related Questions