RE: sync two database in one database

SSC Guru

Points: 148809

January 18, 2017 at 3:29 am

#1923366

elhamazizi5456 - Wednesday, January 18, 2017 3:08 AM
John Mitchell-245523 - Wednesday, January 18, 2017 2:34 AM
elhamazizi5456 - Wednesday, January 18, 2017 12:37 AM
hello all.
I have two Same Data base in structure not in Data( as Test1,Test2) and one Data base with different structure as Test3.
in addition I Have 3 Tables (Users,Roles,Groups) in Test1,2,3 .Now I need to sync this 3 Tables in Test3 from Test1,2.
in addition Test1,3 is on server1 and Test2 is on Server2.
Which the best way can help me?
thanks<
Is this a one-off exercise, or a regular occurrence? Are the table definitions for the three tables identical on all three servers? Please post DDL for the tables, including constraints, and perhaps a few rows of sample data for each.
John
hi thank you for your reply
It is a regular occurrence and The table definitions for this tables are the same in test1,2 and different in Test3.
this code in test1,test2:
CREATE TABLE [Office].[Users](
    [Id] [INT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [UserName] [NVARCHAR](150) NOT NULL,
    [Password] [NVARCHAR](100) NOT NULL,
    [Enabled] [BIT] NOT NULL CONSTRAINT [DF_Users_Enabled] DEFAULT ((1)),
    [ChangePasswordOnFirstLogin] [BIT] NOT NULL CONSTRAINT [DF_Users_ChangePasswordOnFirstLogin] DEFAULT ((1)),
    [PasswordComplexity] [BIT] NOT NULL CONSTRAINT [DF_Users_PasswordComplexity] DEFAULT ((0)),
    [DeveloperAccount] [BIT] NOT NULL CONSTRAINT [DF_Users_DeveloperAccount] DEFAULT ((0)),
    [Ticket] [VARCHAR](100) NULL,
    [DefaultActorId] [INT] NULL,
    [LastLoginTime] [DATETIME2](1) NULL,
    [LastLogoutTime] [DATETIME2](1) NULL,
    [CreationDate] [DATETIME2](1) NOT NULL CONSTRAINT [DF_UsersInfo_CreationDate] DEFAULT (GETDATE()),
    [FirstName] [NVARCHAR](50) NULL,
    [LastName] [NVARCHAR](50) NULL,
    [NationalCode] [VARCHAR](10) NULL,
    [Sex] [NVARCHAR](20) NULL,
    [BirthDate] [DATE] NULL,
    [BirthState] [INT] NULL,
    [BirthCity] [INT] NULL,
    [IsOnline] [BIT] NOT NULL CONSTRAINT [DF_Users_IsOnline] DEFAULT ((0)),
    [IsLocked] [BIT] NOT NULL CONSTRAINT [DF_Users_IsLocked] DEFAULT ((0)),
    [LockTime] [DATETIME] NULL,
    [LoginRetries] [INT] NOT NULL CONSTRAINT [DF_Users_LoginRetries] DEFAULT ((0)),
CONSTRAINT [PK_UsersInfo] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
this code in test3:
CREATE TABLE [dbo].[Users](
    [User_ID] [INT] IDENTITY(1,1) NOT NULL,
    [FirstName] [NVARCHAR](100) NULL,
    [LastName] [NVARCHAR](100) NULL,
    [UserName] [NVARCHAR](100) NULL,
    [Password] [NVARCHAR](100) NULL,
    [NewPassword] [NVARCHAR](100) NULL,
    [IsActive] [BIT] NOT NULL CONSTRAINT [DF_Users_IsActive] DEFAULT ((1)),
    [IsOnline] [BIT] NOT NULL CONSTRAINT [DF_Users_IsOnline] DEFAULT ((0)),
    [IsConfirm] [BIT] NULL CONSTRAINT [DF_Users_IsConfirm] DEFAULT ((0)),
    [LastLoginTime] [DATETIME] NULL,
    [Gender] [BIT] NULL,
    [MarriedStatus] [BIT] NULL,
    [BirthDate] [DATETIME] NULL,
    [CountryNo] [SMALLINT] NULL,
    [LanguageNo] [TINYINT] NULL,
    [Address] [NVARCHAR](250) NULL,
    [E_Mail] [NVARCHAR](100) NULL,
    [PhotoPath] [NVARCHAR](200) NULL,
    [CreationDate] [DATETIME] NULL CONSTRAINT [DF_Users_CreationDate] DEFAULT (GETDATE()),
    [EditionDate] [DATETIME] NULL,
    [AcceptXML] [NTEXT] NULL,
    [LastRefreshTime] [DATETIME] NULL CONSTRAINT [DF_Users_LastRefreshTime] DEFAULT (GETDATE()),
    [LastChangePassDate] [DATETIME] NULL,
    [NativeID] [NVARCHAR](200) NULL CONSTRAINT [DF_Users_NativeID] DEFAULT (''),
    [PersonnelID] [NVARCHAR](200) NULL,
    [CRC] [CHAR](40) NULL,
    [ActiveSessionID] [VARCHAR](200) NULL,
    [SystemProfile] [XML] NULL,
    [IPLocation] [VARCHAR](20) NULL,
    [DepartmentID] [INT] NOT NULL CONSTRAINT [DF_Users_DepartmentID] DEFAULT ((-1)),
    [Phone] [VARCHAR](20) NULL,
    [Mobile] [VARCHAR](20) NULL,
    [UserType] [INT] NULL,
    [Title] [NVARCHAR](100) NULL,
    [Description] [NVARCHAR](2048) NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
    [User_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
It should be noted that Test1 , test3 database are on Server1 and Test2 DataBase is on server 2.

That's only one of the tables, but OK, let's start with that. Your source table has considerably fewer columns than your destination. Have you decided how you're going to map the columns between the two tables, and what values you're going to put in the destination columns that don't map? Are there any foreign key constraints? Is there already any data in the destination table? Do you want the destination table to have the same values of User_ID as the values of Id in the source tables? Do you even need the User_ID column at all, given that there appear to be no foreign key constraints? Are any values of Id duplicated between Test1 and Test2? Are any identical users defined in both Test1 and Test2? Do you really store passwords in plain text? What is each database actually used for?

You might consider going back to the drawing board and redesigning the whole thing, because it looks as if you're trying to force the proverbial square peg into a round hole.

John