I would like to replicate a table (call this table
source table) to a read-only table (call this table
dest table) in the same database. I have only found information regarding publishing and subscribing across different databases. Is functionality to replicate a table in the same database supported in Microsoft SQL Server?
This need arises from the fact that we have a very complicated and not easy to change process that updates and inserts records into the
source table, and since this process was created, we've developed a need to query the
source table, which has caused intermittent conflicts (i.e. our process that updates the
source table is temporarily blocked while other queries are running). And we need to avoid these blocks/delays to our process. Our queries on the
source table are always lower priority.
All of the queries that run against the
source table are queried on indexed columns for speed/efficiency and minimal table locking and most of the queries that are run against the
source table are completed
with (nolock) hint for the same reason. Yet we still encounter locking problems that delay our process that updates and inserts new records to the
Based on everything I've read regarding transactional replication, this should allow us to query the
dest table as much as we want without locking the
source table. The problem with what I know about replication is that I would like my
source table and
dest table to reside in the same database.
Worst case, I can replicate the
source table to another database on the same server and query across databases. I would prefer to avoid being forced to use two separate databases if there exists an option to complete this on the same database.
If you don't mind your query table being somewhat behind your source table, why don't you create a stored procedure that:
Then just schedule a task to run your stored procedure every hour or however often you want to update your query table (ie, once every 4 hours, every 30 minutes, etc).
SQL Server is very fast at copying one table's contents into another table. Truncating your query only table first will be a very fast action in itself, and will prevent duplicate records. You might want to consider just dropping the query only table instead, and then letting SQL Server build the query only table as it inserts the contents of the source table. That is also quite fast. This select statement takes the form of
Select * Into ReadOnlyTable From SourceTable. You can look up more information in your favorite search engine using the search term "SQL Select Into statement" (or just go over to http://www.w3schools.com/sql/sql_select_into.asp and read about it there if you don't want to be bothered with Googling it yourself).
You can't. Not without aliases and other tricks.