merge data

  • hi there

    i have 5 database with same table schema in my branches

    i want to have one data base in my head office and store all branches data to it with replication or another way

    and my head office data base be update with branches every one hour

    how can i do that??

    thank you

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • The question is too generic. There are so many ways to achieve the expected outcome.

    what did you try and what stopped you on the way?

    _____________
    Code for TallyGenerator

  • You're also running 2008, which is more than a decade old and hasn't been supported for years.  I strongly recommend that you develop an upgrade plan.

    --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)

  • Thanks for your reply

    Which replication method can i use to do this without overwrite another database data?

    And what can I do with pk column conflict  in table?

    I tried use merge replication but last database overwrite another

    I want something like insert command that added data to last row without pk conflict

    thank you

  • I know this sounds a bit "smarty pantsed" but, in order to not have PK conflicts, you have to stop sending or pulling... PK conflicts.

    In other words, you need to plan out a different PK.  Perhaps adding a "branch" column to the tables and your PK will solve the issue.

    I can't make much more of a suggestion that that because you've simply not posted enough information to help.

    --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)

  • I prefer to ditch original rid's and upload the data using natural keys, with new rid generated within the "merged" tables.

    unless, it's important to keep a record of the source of each particular row, then adding a "BranchId" is kind of a no brainer.

    _____________
    Code for TallyGenerator

  • this is one of my table in database design

    USE [test]

    GO

    /****** Object: Table [dbo].[Account] Script Date: 11/05/2020 11:18:58 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[Account](

    [id] [int] NOT NULL,

    [dscrm] [nvarchar](20) NOT NULL,

    [ObjVer] [int] NOT NULL,

    [Name] [nvarchar](255) NULL,

    [Path] [nvarchar](255) NULL,

    [IsLeaf] [bit] NULL,

    [IsIdle] [bit] NULL,

    [Comments] [nvarchar](255) NULL,

    [UnMoveable] [bit] NULL,

    [UnDeletable] [bit] NULL,

    [UnRenameable] [bit] NULL,

    [UnRecodeable] [bit] NULL,

    [ParentID] [int] NULL,

    [Code] [bigint] NULL,

    [BriefDsc] [nvarchar](50) NULL,

    [Nature] [int] NULL,

    [HasSubSb] [bit] NULL,

    [HasCenter] [bit] NULL,

    [HasProject] [bit] NULL,

    [TTMSChapterType] [int] NULL,

    [LedgerID] [int] NULL,

    PRIMARY KEY CLUSTERED

    (

    [id] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[Account] WITH NOCHECK ADD CONSTRAINT [FK2DC31649B2C0F5D7] FOREIGN KEY([ParentID])

    REFERENCES [dbo].[Account] ([id])

    NOT FOR REPLICATION

    GO

    ALTER TABLE [dbo].[Account] CHECK CONSTRAINT [FK2DC31649B2C0F5D7]

    GO

     

    all database tables have same schema

     

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

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