Hello,

I am looking for a data driven stored procedure that inserts data from one database to another. I will calling this stored proc once every 10 seconds to insert data. I can do this with TX replication but I want to avoid this because the source and destination database will be on same server and also there is already transaction replication setup with distribution, subscription and publication on the same server.

These are 3 tables with foreign key dependencies. Here is DDL

CREATE TABLE [dbo].[ListedProcess](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[TriggerId] [uniqueidentifier] NOT NULL,
	[ListedProcessStatusId] [int] NOT NULL,
	[ListedAt] [datetime] NOT NULL,
	[LastUpdatedAt] [datetime] NULL,
	[IsLiveMode] [bit] NOT NULL,
	[CallProcessId] [uniqueidentifier] NULL,
	[EmailProcessId] [uniqueidentifier] NULL,
	[TextProcessId] [uniqueidentifier] NULL,
	[NotificationTemplateId] [uniqueidentifier] NULL,
	[Size] [int] NOT NULL,
	[ResultsExported] [bit] NOT NULL,
	[JobCompletedEmailSent] [bit] NOT NULL,
	[SubStatusId] [int] NULL,
	[Preprocessor] [varchar](50) NULL,
 CONSTRAINT [PK_JobQueue] PRIMARY KEY NONCLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[ListedProcessPerson](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ListedProcessID] [int] NOT NULL,
	[SourcePersonIndex] [int] NOT NULL,
 CONSTRAINT [PK_ListedProcessData] PRIMARY KEY NONCLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[ListedProcessPersonAttribute]    Script Date: 7/27/2015 11:55:16 AM ******/

CREATE TABLE [dbo].[ListedProcessPersonAttribute](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ListedProcessPersonID] [int] NOT NULL,
	[SourceColumnIndex] [int] NOT NULL,
	[Value] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_ListedProcessPersonAttribute] PRIMARY KEY NONCLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[ListedProcessPerson]  WITH CHECK ADD  CONSTRAINT [FK_ListedProcessPerson_ListedProcess] FOREIGN KEY([ListedProcessID])
REFERENCES [dbo].[ListedProcess] ([ID])
GO
ALTER TABLE [dbo].[ListedProcessPerson] CHECK CONSTRAINT [FK_ListedProcessPerson_ListedProcess]
GO
ALTER TABLE [dbo].[ListedProcessPersonAttribute]  WITH CHECK ADD  CONSTRAINT [FK_ListedProcessPersonAttribute_ListedProcessPerson] FOREIGN KEY([ListedProcessPersonID])
REFERENCES [dbo].[ListedProcessPerson] ([ID])
GO
ALTER TABLE [dbo].[ListedProcessPersonAttribute] CHECK CONSTRAINT [FK_ListedProcessPersonAttribute_ListedProcessPerson]
GO

Thanks for the inputs.

This is an article I wrote for SQL Server Pro magazine a few years back which may help:

http://sqlmag.com/sql-server/migrating-data-database-database

Thank

Carl

CREATE PROC yourProc
AS
BEGIN	
    MERGE TARGETDB.dbo.table1 AS target
    USING SOUCEDB.dbo.table1 AS source 
    ON 
    WHEN MATCHED THEN 
	.
	.;

	MERGE TARGETDB.dbo.table2 AS target
    USING SOUCEDB.dbo.table2 AS source 
    ON 
    WHEN MATCHED THEN 
	..
	;
	-and so on
END
GO

If you have large amount of data in Table1 then copying data to table2 may take some time. I would rather suggest you to copy row to table2 (your destination) as soon as you insert in your table1 

Note: With table1 I mean set of tables which you have mentioned.

You can take advantage ot OUTPUT Clause while inserting, updating or deleting data from table1

Ex:

INSERT INTO [DATABASE1].[SCEHMA].[TABLE1] ([COL1], COL2...)
OUTPUT inserted.COL1, inserted.COL2... into [DATABASE2].[SCHEMA].[TABLE2]
select [COL1], [COL2].... FROM 
[external_table]

Do you think you can do cascade update for these three tables within 10 seconds? 

If you just want to have replicated table then don't create any key relationship on target table. In fact, don't also create any index on that too as your destination table will be used just for insert and heap will be best for this case.

Hi SQLSPUser,

For the case the two databases are in the same instance, you can run MERGE statements to insert/update/delete data against the tables in the target datebase one by one from the tables with foreign key(FK) to the ones referencing FK in the stored procedure(SP) and schedule the SP to run every 10 seconds.

CREATE PROC yourProc
AS
BEGIN	
    MERGE TARGETDB.dbo.table1 AS target
    USING SOUCEDB.dbo.table1 AS source 
    ON 
    WHEN MATCHED THEN 
	.
	.;

	MERGE TARGETDB.dbo.table2 AS target
    USING SOUCEDB.dbo.table2 AS source 
    ON 
    WHEN MATCHED THEN 
	..
	;
	-and so on
END
GO

For the case the two databases are in different instances, use linked server and specifying the target table name like targetServer.targetDB.dbo.table1.

If you have any feedback on our support, you can click here.