Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a common product table in db1 and db2. I want to make it one table because it has one inventory (stock of products). What is the best solution to share product table among two different database? I have few thoughts:

  1. Writing trigger on update/delete/insert one table and update another same time.
  2. Creating view on product table and used in second db (I don't know how)
  3. replication (I don't know how)

Can you guide me on the best way?

share|improve this question
1  
If you have two databases on the same instance, why do you need two copies of the data? Seems better to just reference a single copy from as many databases as you need to (and you can use synonyms as in @JNK's answer, but this is unnecessary and merely for coding convenience). –  Aaron Bertrand Jul 2 '13 at 12:40
    
@AaronBertrand I can think of a couple of scenarios. If you have a standard set of functions or procs, it is easier to maintain one copy of these. If you used a three part name in one DB and a two part name in another, you need to maintain two sets of code. Synonyms let you keep one code base because the object names in the current DB context can be identical. –  JNK Jul 2 '13 at 15:16

2 Answers 2

up vote 8 down vote accepted

I would just make a synonym in db2 (assuming the table exists in db1). This will let you point directly to the source table without obfuscating behind a view, and will prevent you from needing to maintain a view if the schema of the table changes:

CREATE SYNONYM dbo.CommonTableName FOR db1.dbo.CommonTableName
share|improve this answer

Create a synonym in the second database that points to the table in the first database. This way you can use the table from both databases for any operation (Insert, update, delete, select etc) seamlessly.

    CREATE DATABASE FirstDB ;
    CREATE DATABASE SecondDB ;
    GO
    USE FirstDB;
    GO
    CREATE TABLE Products(id INT IDENTITY PRIMARY KEY);
    GO
    USE SecondDB;

    -- Create a synonym to be able to reach the product table from the second database
    CREATE SYNONYM dbo.Products FOR FirstDB.dbo.Products;

    -- Add some values to the common table, available from both databases
    INSERT Products DEFAULT VALUES;
    INSERT Products DEFAULT VALUES;
    INSERT Products DEFAULT VALUES;

    -- any statement is available for both databases
    SELECT * FROM FirstDb.dbo.Products;
    SELECT * FROM SecondDB.dbo.Products;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.