CODEWITHSUNDEEP
sql-serverreplication
practicalSQL
practicalSQL

Reputation: 33

Table replication in the same database

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 source table.

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.

Upvotes: 2

Views: 4171

Answers (2)

StarPilot
StarPilot

Reputation: 2272

If you don't mind your query table being somewhat behind your source table, why don't you create a stored procedure that:

  1. Truncates the query only table.
  2. Inserts your source table into your query only table.

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).

Upvotes: 0

Ilan
Ilan

Reputation: 29

You can't. Not without aliases and other tricks.

Upvotes: 1

Related Questions