Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    Regular Coder
    Join Date
    Sep 2009
    Location
    Calgary, Alberta
    Posts
    228
    Thanks
    45
    Thanked 3 Times in 3 Posts

    Can you Insert to 2 databases

    Hey all,


    Can you insert data into 2 databases at the same time?

    Thanks, Slayer

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Short answer: No.

    Longer answer: Depends on what you mean by "at the same time." If you mean "truly simultaneously" the answer is still no. If you mean "within milliseconds of each other and with the whole thing wrapped in a single transaction so that is one insert fails the other will as well" then the answer is yes.

    But you can't do it with a single SQL statement. You could do it by creating a stored procedure that would insert into one and then into the other. And do it all in a single TRANSACTION.

    Curiosity: Why would you do this with two separate DATABASES? I can see doing it with separate TABLES in the same database, but it would be highly unusual to do this across two DBs.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    Sep 2009
    Location
    Calgary, Alberta
    Posts
    228
    Thanks
    45
    Thanked 3 Times in 3 Posts
    Thanks for the reply...


    That is what I was hoping for...

    I am looking at have one as a global database that serves to the website... but another that for current use that can be emptied...

    What are your thoughts to this?


    or is there a better way?

    Slayer.

  • #4
    Regular Coder
    Join Date
    Apr 2012
    Location
    St. Louis, MO
    Posts
    985
    Thanks
    7
    Thanked 101 Times in 101 Posts
    The only reason I can think of for doing this would be to have one as a production database and another as the development database. But you don't have to query two databases, for that. Keep them separate and just code the pages to use one if in production, the other if in development.
    ^_^

    If anyone knows of a website that can offer ColdFusion help that isn't controlled by neurotic, pedantic jerks* (stackoverflow.com), please PM me with a link.
    *
    The neurotic, pedantic jerks are not the owners; just the people who are in control of the "popularity contest".

  • Users who have thanked WolfShade for this post:

    SlayerACC (01-08-2013)

  • #5
    Regular Coder
    Join Date
    Sep 2009
    Location
    Calgary, Alberta
    Posts
    228
    Thanks
    45
    Thanked 3 Times in 3 Posts
    This is what the databases are..

    database 1) Vehicle inventory... loaded by importing.

    database 2) Vehicle Global database. loaded by importing.


    I load the on vehicle inventory db (1) and then I want it to goto the global database db(2) to store in our db if the vehicle comes back to us for sale again.. I can pull it out via the vin # in the global db(2)

    What would be the best procedure to have them import to both databases at the same time.. and if there is a better way I would love to know.


    Thanks, S.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Quote Originally Posted by SlayerACC View Post
    I am looking at have one as a global database that serves to the website... but another that for current use that can be emptied...
    ??? I would think that the one for "current use" would be the one that you would indeed use to server the website.

    Surely you want the most recent data to be served to the website?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    SlayerACC (01-08-2013)

  • #7
    Regular Coder
    Join Date
    Sep 2009
    Location
    Calgary, Alberta
    Posts
    228
    Thanks
    45
    Thanked 3 Times in 3 Posts
    Yes you are correct..

    I will be using the current.. as the main database.. and have the secondary db as the db that holds all the information for all of the vehicles that come into our locations.

    the main database will be erased and readded on a weekly time frame based on the new arrivals.

    So I hope this is a good way of doing so..

    Slayer.

  • #8
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,479
    Thanks
    0
    Thanked 635 Times in 625 Posts
    It would be easier to drop and recreate specific tables on a weekly time frame and keep everything in the one database. That way the weekly job lists the tables to be cleared (which is easier than deleting and recreating a database) and everything else just accesses everything in the one database.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • Users who have thanked felgall for this post:

    SlayerACC (01-08-2013)

  • #9
    Regular Coder
    Join Date
    Sep 2009
    Location
    Calgary, Alberta
    Posts
    228
    Thanks
    45
    Thanked 3 Times in 3 Posts
    So are you say this..


    1 database..


    1 table that can be emptied on a weekly or bi-weekly basis

    and a 2nd table that stores all the data.

    if yes>>

    how would I go about inserting records to the 2 tables at the same time.

    as I currently import with php a 55 record table on a weekly basis to the current table.. and would like to have it go to the now global table as well.

    Now this only happens during the import process..

    this wasy I have a global table that can have every record excluding duplilcates and another that I can use to empty for current data.

    Slayer.

  • #10
    Regular Coder
    Join Date
    Sep 2009
    Location
    Calgary, Alberta
    Posts
    228
    Thanks
    45
    Thanked 3 Times in 3 Posts
    Thanks everyone...

    I have now got it working where I can import my CSV file to database tables.


    Thanks..

    Slayer.


  •  

    Posting Permissions

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