Insert "left join" query results into new database

Question

Insert "left join" query results into new database

thotvedt

SSCommitted

Points: 1915
More actions
August 7, 2014 at 12:34 pm

#294036

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.

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

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

danrst171 Mr or Mrs. 500 Points: 571 More actions · Answer 1

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

Chrissy321 SSCoach Points: 15766 More actions · Answer 2

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

danrst171 Mr or Mrs. 500 Points: 571 More actions · Answer 3

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

Chrissy321 SSCoach Points: 15766 More actions · Answer 4

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

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

thotvedt SSCommitted Points: 1915 More actions · Answer 5

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?

Chrissy321 SSCoach Points: 15766 More actions · Answer 6

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?

thotvedt SSCommitted Points: 1915 More actions · Answer 7

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.

Chrissy321 SSCoach Points: 15766 More actions · Answer 8

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.