SQL Server – Why Existing Data Doesn’t Replicate with Custom Stored Procedures

I am setting up a transactional replication in SQL Server. I am using custom stored procedures for the insert, update, and delete. I also am not replicating schema to the target, a table is already there. The tables do not match as the target contains audit columns. In the SP the values are all mapped.

When I run the snapshot it completes and there are no rows in undistributed. When I check the target table, no data has been replicated.

If I go and perform a transaction on source, it is replicated to the target. Why would the existing data not replicate?

Samples below, if you need something else from this let me know.

Source Table:

CREATE TABLE [dbo].[Table_1](
[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Value1] [varchar](50) NULL,
[Value2] [varchar](50) NULL,
CONSTRAINT [PK_Table_1] 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]

Target Table:

CREATE TABLE [dbo].[Table_1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SRC_ID] [int] NOT NULL,
[VALUE1] [varchar](50) NULL,
[VALUE2] [varchar](50) NULL,
[SRC_DELETE] [bit] NOT NULL,
[TRAN_DT] [datetime] NOT NULL,
[PROCESS_FLAG] [bit] NOT NULL,
CONSTRAINT [PK_Table_1] 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].[Table_1] ADD  CONSTRAINT [DF_Table_1_SRC_DELETE]  DEFAULT ((0)) FOR [SRC_DELETE]
GO

ALTER TABLE [dbo].[Table_1] ADD  CONSTRAINT [DF_Table_1_TRAN_DT]  DEFAULT (getdate()) FOR [TRAN_DT]
GO

ALTER TABLE [dbo].[Table_1] ADD  CONSTRAINT [DF_Table_1_PROCESS_FLAG]  DEFAULT ((0)) FOR [PROCESS_FLAG]
GO

Delete SP

Create procedure [dbo].[rep_del__Table_1]
@pkc1 int
as
begin
declare @primarykey_text nvarchar(100) = ''
insert into [Target].dbo.Table_1
(SRC_ID, SRC_DELETE)
values (@pkc1, 1)
if @@rowcount = 0
if @@microsoftversion>0x07320000
Begin

set @primarykey_text = @primarykey_text + '[ID] = ' + convert(nvarchar(100),@pkc1,1)
exec sp_MSreplraiserror @errorid=20598, @param1=N'[dbo].[Table_1]', @param2=@primarykey_text, @param3=13234
End
end

Insert SP

CREATE PROCEDURE [dbo].[rep_ins__Table_1]
@c1 int,
@c2 varchar(50),
@c3 varchar(50)
as
begin
insert into [Target].dbo.Table_1
(SRC_ID, VALUE1, VALUE2)
values (@c1, @c2, @c3)
end

Update SP

CREATE procedure [dbo].[rep_upd__Table_1]
@c1 int,
@c2 varchar(50),
@c3 varchar(50),
@pkc1 int
as
begin  
declare @primarykey_text nvarchar(100) = ''
insert into [Target].dbo.Table_1
(SRC_ID, VALUE1, VALUE2)
values (@pkc1, @c2, @c3)
if @@rowcount = 0
if @@microsoftversion>0x07320000
Begin

set @primarykey_text = @primarykey_text + '[ID] = ' + convert(nvarchar(100),@pkc1,1)
exec sp_MSreplraiserror @errorid=20598, @param1=N'[dbo].[Table_1]', @param2=@primarykey_text, @param3=13233
End
end

sp_addpublication

exec sp_addpublication @publication = N'Sample', @description = N'Transactional publication of database ''Source'' from Publisher ''DESKTOP''.', 
@sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', 
@snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', 
@add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'false', 
@allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 0, 
@allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'

sp_addarticle

exec sp_addarticle @publication = N'Sample', @article = N'Table_1', @source_owner = N'dbo', @source_object = N'Table_1', @type = N'logbased', 
@description = N'', @creation_script = N'', @pre_creation_cmd = N'truncate', @schema_option = 0x000000000203008D, @identityrangemanagementoption = N'manual', 
@destination_table = N'Table_1', @destination_owner = N'dbo', @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL [rep_ins__Table_1]', 
@del_cmd = N'CALL [rep_del__Table_1]', @upd_cmd = N'CALL [rep_upd__Table_1]'

sp_addsubscription

exec sp_addsubscription @publication = N'Sample', @subscriber = N'DESKTOP', @destination_db = N'Target', @subscription_type = N'Push', 
@sync_type = N'replication support only', @article = N'all', @update_mode = N'read only', @subscriber_type = 0