Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert "left join" query results into new database Expand / Collapse
Author
Message
Posted Thursday, August 7, 2014 12:34 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 11, 2014 1:29 PM
Points: 31, Visits: 121
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.
Post #1600819
Posted Friday, August 8, 2014 8:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 21, 2014 9:37 AM
Points: 247, Visits: 236
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
Post #1601200
Posted Friday, August 8, 2014 8:21 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 4:19 PM
Points: 656, Visits: 3,935
Is your source and destination database on the same server or different servers?
Post #1601201
Posted Friday, August 8, 2014 8:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 21, 2014 9:37 AM
Points: 247, Visits: 236
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
Post #1601205
Posted Friday, August 8, 2014 8:37 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 4:19 PM
Points: 656, Visits: 3,935
Ah yes I shouldn't speed read through posts....

Sounds like a job for SSIS or possibly a linked server.
Post #1601213
Posted Friday, August 8, 2014 9:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 11, 2014 1:29 PM
Points: 31, Visits: 121
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?
Post #1601221
Posted Friday, August 8, 2014 9:09 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 4:19 PM
Points: 656, Visits: 3,935
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?
Post #1601225
Posted Friday, August 8, 2014 9:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 11, 2014 1:29 PM
Points: 31, Visits: 121
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.
Post #1601234
Posted Friday, August 8, 2014 9:41 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 4:19 PM
Points: 656, Visits: 3,935
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.
Post #1601242
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse