Link to home
Start Free TrialLog in
Avatar of Michael Franz
Michael Franz

asked on

SQL save table from Query

Hello,

I have a program that will read a SQL table. It has a very nice interface to it. In SQL I have 2 tables that I want to create a UNION ALL Query. See the query below. I have tested it in SQL and it works. How do I saw that as a table to use my report writing program against it. Also, how to I schedule a job so that query runs and updates the table created.

SELECT NewBusinessHeader.Exec_Name, NewBusinessHeader.Client_Last_Name, NewBusinessLineItem.EffectiveDate, NewBusinessLineItem.Carrier, NewBusinessLineItem.LineOfBusiness, NewBusinessLineItem.SoldPremium, NewBusinessLineItem.Sold, NewBusinessHeader.Referral_Source,NewBusinessHeader.Branch,NewBusinessHeader.Dept
FROM NewBusinessHeader INNER JOIN NewBusinessLineItem ON NewBusinessHeader.NBHeaderID = NewBusinessLineItem.NBHeaderID
UNION ALL
SELECT Submissions.Exec_Name, Submissions.Client_Last_Name,Submissions.EffectiveDate,Submissions.Carrier, Submissions.LineOfBusiness,Submissions.SoldPremium,Submissions.Sold,Submissions.Referral_Source,Submissions.Branch,Submissions.Dept
FROM Submissions

Open in new window

Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Hi,

You may be better off creating a VIEW. You can simply prefix your query with "CREATE VIEW your_view_name AS" ... if you want to create a table, I would create table structure ahead of time. Using a scheduled job, TRUNCATE the table, and use INSERT INTO with your query. You can also use MERGE, depending on your version of SQL. For what you are doing, a VIEW seems most appropriate, though.

Best regards,

Kevin
Avatar of Michael Franz
Michael Franz

ASKER

Kevin,,,,,

Thanks for the thoughts, but I need a little more assistance becasue I am not sure I understand. We are using SQL Express I guess. I am not an IT guy and our IT is not familar with being a DBA.... Let me explain a little more. The 2 tables get updated real time daily through a custom program.


Here are just thoughts. Again, I have no knowledge. I am just saying that.....My "report writing program" can not read SQL, but if I do the UNION ALL query in SQL Express and that query creates a table that updates while the work is being done then my "report writing program" can read that table and I can be the most awesome person in the world to get this report completed.
Kevin,

So... what I am trying to say is I need more help because I have no clue on this. I am an accountant who is desperately trying to create a report. I know that I have SQL code that is above my pay grade, but I got lucky. I am a Newbi!
What I am saying that to most external programs, a VIEW appears like a table. Hence, external applications usually see no difference in "SELECT column_name FROM view_name" and "SELECT column_name FROM table_name". Therefore, I am saying to create a VIEW with your UNION that will update real-time along with the tables. Your application would just use the view as if it were a table, i.e., it won't have to worry that the view is a more complex UNION.

CREATE VIEW view_name_you_want_here 
AS
SELECT NewBusinessHeader.Exec_Name, NewBusinessHeader.Client_Last_Name, NewBusinessLineItem.EffectiveDate, NewBusinessLineItem.Carrier, NewBusinessLineItem.LineOfBusiness, NewBusinessLineItem.SoldPremium, NewBusinessLineItem.Sold, NewBusinessHeader.Referral_Source,NewBusinessHeader.Branch,NewBusinessHeader.Dept
FROM NewBusinessHeader INNER JOIN NewBusinessLineItem ON NewBusinessHeader.NBHeaderID = NewBusinessLineItem.NBHeaderID
UNION ALL
SELECT Submissions.Exec_Name, Submissions.Client_Last_Name,Submissions.EffectiveDate,Submissions.Carrier, Submissions.LineOfBusiness,Submissions.SoldPremium,Submissions.Sold,Submissions.Referral_Source,Submissions.Branch,Submissions.Dept
FROM Submissions
;

Open in new window

How do u create a view in my sql
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you very much for the slow and kind explanation