Post reply

Replicate data of tables fromSQL Server to Postgres SQL

  • May 6, 2019 at 12:45 pm

    #3636403

    Hi all,

    We need to replicate data from SQL Server to Postgres SQL database for limited tables.

    We created a linked server on SQL Server and we are able to insert data from one table of SQL Server to postgres Table using Insert statement below.

    INSERT INTO TEST_POSTGRES.testdb.[public].courtcase_casetype( casetype, status) SELECT  CaseType, Status FROM [dbo].[tblCaseType];

    But when we try to do it using trigger on source table it does not give error but also dont do any thing to destination table

    CREATE TRIGGER dbo.caseTypeInsert ON [dbo].[tblCaseType]
    AFTER INSERT
    AS
    BEGIN
    SET XACT_ABORT ON
    BEGIN DISTRIBUTED TRANSACTION
    INSERT INTO TEST_POSTGRES.testdb.[public].courtcase_casetype( casetype, status) SELECT  CaseType, Status FROM [dbo].[tblCaseType];
    END
    go

    Please suggest.

     

  • May 7, 2019 at 3:05 pm

    #3637203

    1. I think distributed transactions will only work between two instances of SQL Server.

    2. The code should be SELECT CaseType, Status FROM inserted not SELECT CaseType, Status FROM [dbo].[tblCaseType].

    3. It is a bad idea to have a trigger using a remote instance/server unless you really need it to be synchronous. (ie If the link goes down your application stops.) The simplest way to create an asynchronous trigger is to create a local table, say CaseTypeQ, and insert into that from the trigger. Then create an agent job to process the local table every five minutes.

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

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