Create Duplicate SQL Server Table with Primary Key

To integrate tables without a Primary Key into your Mosaic replication, follow these steps. Since SQL Server replication requires Primary Keys, you'll implement a workaround:

Solution Overview

1. Create a New Table and Copy Existing Data: :
   • Create a new table in your SQL Server database to hold the replicated data.
   • Copy the structure and data from the existing table into the new table.
2. Add a Primary Key:
   • Define a Primary Key for the new table to meet the SQL Server replication requirements.
3. Sync Data Using Triggers:
   • Create triggers on the original table to ensure data changes (inserts, updates, deletes) are mirrored in the new table. This keeps the new table in sync with the original.
4. Include the New Table in Replication:
   • Use the new table with the Primary Key for your Mosaic replication setup.

1. Create a New Table and Copy Existing Data

Follow the instructions to here to create a new table and copy existing data into it.

• It is recommended to name the new table something like mosaicOriginalTable to make it clear that this table is for your Mosaic replication

2. Add a Primary Key

Follow the instructions here to add a Primary key to your new table.

Example:

Suppose you have a table [Sales].[OrderDetails] and you want to add a primary key constraint on the column OrderDetailID. The statement might look like this:

ALTER TABLE [Sales].[OrderDetails]
   ADD CONSTRAINT PK_OrderDetails_OrderDetailID PRIMARY KEY CLUSTERED (OrderDetailID);

• [Sales].[OrderDetails]: OrderDetails is table you are modifying. Sales is the name of your database.
• PK_OrderDetails_OrderDetailID: The name of the primary key constraint. It is typically like PK_MyTableName_MyColumnName
• PRIMARY KEY CLUSTERED: The type and index of the constraint.
• (OrderDetailID): The column that will be part of the primary key.

3. Create Required Triggers for your table

Follow the instructions here to create the triggers. Note that you do not need to create the two tables included in this guide, you should create the triggers for your existing table without the primary key.

4. Add the New Table in the SQL Replication

Follow the instructions here to include the new table in the SQL replication.