sqldatabase

Pointing old table to updated table in SQL

I am working in a data warehouse and I am looking for a way to have a query that reference a table, start referencing a different table, without having to change the query. We have a set of tables that are brought into our operation data store that are the same as the source table. The ETL process that brings these tables in was updated to version 2, and now we are ready to shut down the version 1 tables.

Currently, there are two sets of tables, version 1 and version 2. An example pair of tables would be ods.STRING_LOCALE and ods.STRING_LOCALE_v2. I want to remove the ods.STRING_LOCALE table, but allow any prebuilt queries referencing ods.STRING_LOCALE to automatically begin using ods.STRING_LOCALE_v2. What would be a good strategy to accomplish this?

Solution

  • You can use a Synonym or View
    Option 1: Create a View with the Same Name
    If your database supports views (e.g., SQL Server, Oracle, PostgreSQL), you can:

    1. Drop the original table ods.STRING_LOCALE

    2. Create a view named ods.STRING_LOCALE that selects from ods.STRING_LOCALE_v2

    CREATE VIEW ods.STRING_LOCALE AS
SELECT * FROM ods.STRING_LOCALE_v2;

    Option 2: Use Synonyms (SQL Server, Oracle)

    If you're using a database that supports synonyms:

    CREATE SYNONYM ods.STRING_LOCALE FOR ods.STRING_LOCALE_v2;