Insert "left join" query results into new database

  • I have been given, what is to me, a fairly substantial join query with the instructions of creating a new database and scheduling the updating the new database with the results from that query.

    1. Can I just create a default database and let the query create the necessary tables on the first run and then let it update them in the future?

    2. Being fairly new to performing complex (again, complex to me) T-SQL queries, what kind of language/syntax would I add to this join query to have it port it's results to another database on a different server?

    The join query pulls from 6 tables on the source database.

    Thanks for any help. Researching the specifics of doing this has led to varied results, and I'm hoping it's much simpler than it looks.

  • I am by no means an expert, but from your explanation I'd suggest a simple SSIS package with data flow, which is called by a scheduled job.

    -------------------------------------------------
    Trainee DBA

  • Is your source and destination database on the same server or different servers?

  • thotvedt (8/7/2014)


    2. Being fairly new to performing complex (again, complex to me) T-SQL queries, what kind of language/syntax would I add to this join query to have it port it's results to another database on a different server?

    this made me believe that he wants to move the data to another server.

    -------------------------------------------------
    Trainee DBA

  • Ah yes I shouldn't speed read through posts....

    Sounds like a job for SSIS or possibly a linked server.

  • What about using transactional replication to create a subscriber database on the destination server of only the tables I need to run the join query against?

  • I hear transactional replication is complicated and difficult to support.

    http://www.brentozar.com/archive/2013/09/transactional-replication-change-tracking-data-capture/

    How often do you need to refresh the destination server?

  • Probably daily. The only reason I go that route is because I actually have some experience with replication, and I have none with creating SSIS packages.

  • If you record sets are fairly small, your networking robust and your security context allows it, linked severs can be a reasonable solution. Performance and troubleshooting can be difficult.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply