Posted by JoshG on 12/04/07 14:44

Erland,

Thank you for this response. I appreciate it.

There is one complicating factor I neglected to mention.

In several tables, we have columns currently poorly named "ID_ref" or
"CustomerID_ref". In the new version of the database, we have simply
renamed these to ID or CustomerID.

Using a view, we can easily keep using the ID_ref but map it to the
new physical ID column in the new database.

From looking at synonyms so far, I don't think you can do this kind of
mapping. Do you know if that is the case for sure?

best regards,
Josh

On Dec 3, 5:52 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> JoshG (jsgo...@gmail.com) writes:
> > To ensure compatibility with the existing code-base, we'd like to
> > determine whether we can use updatable views to allow the current code
> > to continue to run against the existing DB.
>
> Since you apparently are on SQL 2005, I have a better idea: synonyms.
>
> CREATE SYNONYM LogTable FOR databaseB.dbo.LogTable
>
> and you are done.
>
> > In database A, we'd rename LogTable to LogTable-Old, and create a view
> > called LogTable which points to DatabaseB.dbo.LogTable.
>
> Using the transaction log would require that you have a log reader
> like Lumigent's Log Explorer to read the log. But since would be a one-
> off, it seems to me that you could use a trigger to pick up the changes
> while the copy is running.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Can anyone help with waittype 0x0044?
• Prev in forum: RE: SELECT * FROM "multiple_tables" WHERE "field_(in_those_tables)_have_the_same_paricular_value" ?
• Thread view: Re: Using updatable views when moving tables from one DB to another?

[Reply to this message]