Need an example of a Stored Procedure that combines two different databases into one temp table for reporting purposes.

Need some examples or example not too big and overwhelming but a system that shows syntax etc. of the combining of two databases with multiple tables if possible that would show how they are pushed into a temporary table and used for reporting purposes. I got the reporting end just not the stored procedure syntax. Please let me know if you have anything like that. Thank you.
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give us some more details of exactly why you need this, as so far this smells like homework.
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
I have a client who wants to combine two DBs. A Macola database and a SQL Server database. They seem to think the only way to go is to create a stored procedure that combines the two into a temp table and then it is used to write a Crystal Report off of.

Some problems are the field lengths are different.
Some of the field types are different.
I have written stored procedures but never ones like this.

I am open to alternative solutions as a suggestion to the client but if there is a way to do this or if you have examples I need to try. I am open to either I have never had such difficulty with data ever.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>A Macola database and a SQL Server database.
Haven't heard of Macola, so I can't speak to that.

Immediate answer: You can use SSIS to import data from two different sources (Macola / SQL) into a single destination.  This implies a host of issues you may or may not have to deal with:  Differing file types, data quality, data completeness,  if the data can be joined, etc.   Then this new db can be modified however you want to be optimized for reporting.  

afaik a SQL Server SP can connect to different databases within a server, and different SQL servers if you're using linked servers, but not different types of database applications.

Long-term answer:  I'd get more info on what the client is expecing here.  How many reports are we talking?  What is the timing (monthly, daily, hourly) of the changes in the data and how fast reports need to contain updated data?  This could be an entire data warehouse project if you're not careful.
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
They are both SQL Server back end Dbs.....
24 reports....
Does SSIS come with SQL Server 2008 R2...? If so where...?
Would it be possible to set up a linked server to the Macola database (which as you indicated is SQL Server)? If so, then you should be able to run queries, and even do JOINs and UNIONs between tables in both databases.

As others have indicated, your description is very vague. When you say column types are different, lengths are different, are you speaking in reference to columns that you want to JOIN on, or are you going to be including UNION statements to bring together 'common' data? Depending on data types and what you are trying to accomplish, you can either let SQL Server to implicit conversions or, if that is not possible, do it explicitly with the CAST() function.
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
I am speaking of the columns and or fields I want to put into a temp table so that when I access the SP the data is already merged from the past system Macola into the New SQL Server DB.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
ruavol2 -

(1) The major battle here is going to be joining two sets of data from different sources in a way that's decent to report on, and

(2) if you use a temp table within a SP, that means the process that joins this data will be performed every time, which may not be ideal if it takes a long time.

If you can solve these two, whether it's a temp table or a physical table somewhere is a very minor point.

So ... the question now becomes, (which is likely much bigger in scope than a single EE question imho) 'What is the schema(s) for these different data types that need to be joined into one, and how clean is the data?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Thank you gentlemen for the advice.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.