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

Microsoft SQL Server

Avatar of undefined
Last Comment
Michael Franz

8/22/2022 - Mon
Kevin Cross

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
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.
Michael Franz

ASKER
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!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Kevin Cross

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

Michael Franz

ASKER
How do u create a view in my sql
ASKER CERTIFIED SOLUTION
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Michael Franz

ASKER
Thank you very much for the slow and kind explanation
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.