CODEWITHSUNDEEP
sqlsynchronizationbetweenreplicate
Marius Doman
Marius Doman

Reputation: 23

"sync" or replicate between tables

I have a existing table "dbo.Source Table" and I want to make another one [me].[DestinationTable] in which to copy some columns from existing table "dbo.Source Table". I use Microsoft SQL Server Management Studio.

This is what I want, in table "dbo.Source Tabel" I have one row in that I have information in columns "TextColumn", "ValueColumn" etc. For start this information I want to be copied into new table [me].[Destination Table], but when information into table "dbo.Source Tabel" for example in column "TextColumn" change from "MDO" with "Marius", to update automatically in table [me].[Destination Table] and so on for each column and when a row is deleted from dbo.SourceTable, to be deleted and from me.DestinationTable.

enter image description here

CREATE TABLE dbo.SourceTable
    (
    SourcePrimaryKeyID INT IDENTITY PRIMARY KEY,
    TextColumn VARCHAR(2048),
    ValueColumn DECIMAL(18,3),
    NumberColumn INT
    )

CREATE TABLE [me].[DestinationTable]
    (
    DestinationPrimaryKeyID INT IDENTITY PRIMARY KEY,
    SourcePrimaryKeyID INT,
    TextColumn VARCHAR(2048),
    ValueColumn DECIMAL(18,3),
    NumberColumn INT,
    ActionType VARCHAR(100),
    CreatedDate AS GETDATE(), -- Default to current date
    CreatedDatabaseUser AS SUSER_SNAME()
    )

GO

Upvotes: 1

Views: 241

Answers (1)

q4za4
q4za4

Reputation: 652

I do not have a envorionment to test it but:

MERGE me.DestinationTable tgt
USING dbo.SourceTable src
 ON  src.SourcePrimaryKeyID = tgt.DestinationPrimaryKeyID
WHEN MATCHED THEN
  UPDATE SET
    tgt.TextColumn =   src.TextColumn, 
    tgt.ValueColumn =  src.ValueColumn,
    tgt.NumberColumn = src.NumberColumn
WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (
  src.TextColumn, 
  src.ValueColumn,
  src.NumberColumn
);
GO

Upvotes: 1

Related Questions