SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trigger to periodically copy one table to different databae

    Hi:

    is it possible to copy one table (from one database) to another (to different database)?


    The reason is i have 2 databases. Some tables are common to both and I don't want to have to manage different tables for the same data. Data in those tables changes frequently.

    I have this structure.

    MAIN
    MAIN_Case
    MAIN_Meetings

    HTML Code:
    <pre>
    MAIN database is supposed to have some tables like following:
    1) City
    2) Country
    3) State
    4) Location
    5) Persons
    
    
    MAIN_Case
    1) City          (Read Only)
    2) Country     (Read Only)
    3) State        (Read Only)
    4) Location    (Read Only)
    5) Persons     (Read Only)
    6) Cases
    7) Case_Notes
    
    
    MAIN_Meetings
    1) City          (Read Only)
    2) Country     (Read Only)
    3) State        (Read Only)
    4) Location    (Read Only)
    5) Persons     (Read Only)
    6) Meetings
    7) Meeting_Notes
    </pre>
    There might other modules in future. So, i decided to SOMEHOW have one copy of those COMMON tables and then COPY the READ ONLY copies to all the other tables.

    These copies will be updated when there will be a change in the main tables (with some trigger of some sort)

    Is it possible?
    How?

    Thanks
    ---------------------------
    Errors = Improved Programming.
    My Site

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    if they're on the same server, you can just use a view and share the exact table, no copies needed.

  3. #3
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So, what about my tables where I am referencing to that table?

    Persons table holds my user accounts which I have foreign keys to from other tables.

    Is there a way to make a foreign key to a table in different database?
    ---------------------------
    Errors = Improved Programming.
    My Site

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    if you can do that, then you don't even need a view, or any other fancy trickery. so let's say you have two databases named foo and bar:
    Code:
    select f.something
         , b.somethingelse
      from foo.sometable f
      join bar.sometable b
        on b.a = f.a
    the only requirement is that the one user must have access to both databases.

  5. #5
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Let's say I have a user which has complete access to all the databases.

    So, you mean I can have foreign keys to a table in different database as long as I have permission?

    So, what do i do?

    Code:
    Column_Name type references db2.persons(id)
    Thanks
    ---------------------------
    Errors = Improved Programming.
    My Site

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    why don't you try it and see what happen?

  7. #7
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried but I get following error
    Msg 1763, Level 16, State 0, Line 1
    Cross-database foreign key references are not supported. Foreign key 'ice.dbo.persons'.


    I tried creating table like
    Code:
    create table temp
    (
    	id				int		identity,
    	id_person		int		references ice.dbo.persons(id)
    );
    ---------------------------
    Errors = Improved Programming.
    My Site

  8. #8
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i was reading following article:
    http://searchsqlserver.techtarget.co...184503,00.html

    it says:
    1) Cross-database foreign keys do not exist
    2) Schema should be used as they do the same thing I am trying to do and removing the need for cross-database references.

    What do you suggest?
    ---------------------------
    Errors = Improved Programming.
    My Site

  9. #9
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    is this microsoft sql server? i'm not familiar enough with it to offer advice in that area.

  10. #10
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes. ah well hopefully somebody else might help me.

    thanks anyways for your time.
    ---------------------------
    Errors = Improved Programming.
    My Site

  11. #11
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,651
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I happen to know a thing or three about Sql Server. And, to start off with, why are they separate DBs in the first place?

  12. #12
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What we are planning to have is:
    A project with various modules developed INDEPENDENTLY.

    So,
    1) our project will have some common tables to be used by ALL modules WITHOUT creating new one to avoid duplicate data
    2) Every module will have there own tables regardless of what other modules might have. This might generate problem as 2 modules can have a same table name (with different schema)


    So, first we decided to have different databases like even though we knew it's not best approach.

    PROJECT
    MODULE_1
    MODULE_2

    The first idea was to check for cross-database foreign keys but it's not supported in SQL Server.
    Second was to have a STATIC READ-ONLY copy of the COMMON tables in EVERY table.

    for a while we pursued second option, but as I was searching on Google , today I found that article about schema.


    That is all the history till now. we are inclined to go with schema what we are wondering about certain things:
    1) Will it have performance issues?
    2) When reading the same table (as modules won't be able to write into COMMON tables and those will be updated from one location), will we have LOCKING problems?
    3) if yes to (2) then how easy it is to over come it?
    4) We are not sure if this is a good approach as we just started pursuing it.
    ---------------------------
    Errors = Improved Programming.
    My Site

  13. #13
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,651
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Schema should suit this method well, though honestly I would actually design an API around the common elements rather than deal with them on a database level.

    Anyhow, for your questions:
    1) No, SELECTing is SELECTing.
    2) Reading does not lock tables under most circiumstances. Now, if you do some sort of mass update bulk operation, you might end up getting blocked from reading by it or getting unrepeatable reads depending on how you set your transaction levels.
    3) Depends on if there is some sort of huge "import data" process run when the system is supposed to be online and avaliable.
    4) Schemas are a fine approach. A well defined API to the common data is probably a better approach long-term.

  14. #14
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wwb_99 View Post
    Schema should suit this method well, though honestly I would actually design an API around the common elements rather than deal with them on a database level.
    4) Schemas are a fine approach. A well defined API to the common data is probably a better approach long-term.
    Can you please explain this more?

    and other than organizing are there any other advantages of using schema?
    ---------------------------
    Errors = Improved Programming.
    My Site

  15. #15
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,651
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    The entire point of a schema is organization and security management.

    Basically, you are making something where you have a centralized service supplying the MAIN data. Rather than providing this via database tables or views or whatever, I would provide this using a service API. It is alot more abstracted in the long term, though it will require some up-front effort.

  16. #16
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wwb_99 View Post
    The entire point of a schema is organization and security management.

    Basically, you are making something where you have a centralized service supplying the MAIN data. Rather than providing this via database tables or views or whatever, I would provide this using a service API. It is alot more abstracted in the long term, though it will require some up-front effort.
    Sorry, can you please explain what do you mean when you say Service API? Not sure what you mean by that. o you mean by programming or....?

    Can you please provide a simple example?

    Thanks
    ---------------------------
    Errors = Improved Programming.
    My Site


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •