Select Into/Insert Into SQL Server query duplicates

Question

Sorry for asking this question, but I am a beginner in SQL, my colleague at work build a view, which I need as datasource for a report, however since this view is based on several other views it takes like 45 minutes to execute the query. This is way to long. Therefore I created a table from that view, initial execution time is the same, but once in place it executes in seconds.

In Microsoft SQL Server 2014 I used the following query:

select * 
into [dbo].[MAT_v_demnew_daily_am_all_data]
from [dbo].[v_demnew_daily_am]

This works fine, but since the view is updated daily I also need to refresh the table everyday. When I now execute the above mentioned query I get the message that the table already exists.

That's why I tried to use 'insert' in this case:

insert into [dbo].[MAT_v_demnew_daily_am_all_data]
    select * 
    from [dbo].[v_demnew_daily_am]

Here I have the problem that it not only inserts the additional data but also the already existing data, so in the end I have duplicates.

As a workaround I now manually delete the [dbo].MAT_v_demnew_daily_am_all_data] table and then execute the select * into query.

Now I am looking for an easier solution, is it possible to having the table deleted by query and in the same query create a new one by select * into or is it possible to only insert new data from the view to the table so that I don't get duplicates.

Moreover, is it possible to have such SQL statement being executed automatically on a daily basis, maybe by .bat file and windows task scheduler?

I know that the source of all problems is the View and that we should improve that, but looking for a short term solution first.

Thanks so much.

Mathias

Answer 1

Try this:

IF OBJECT_ID('dbo.MAT_v_demnew_daily_am_all_data', 'U') IS NOT NULL 
DROP TABLE dbo.MAT_v_demnew_daily_am_all_data 
SELECT INTO dbo.MAT_v_demnew_daily_am_all_data FROM dbo.v_demnew_daily_am

This query is reusable on a daily basis.

You can create one stored procedure including this query.

Then you only need to execute the stored procedure.

---

Updated

Before you create the stored procedure, please check if you have the permission.

Then try:

create procedure [procedure_name]
as
IF OBJECT_ID('dbo.MAT_v_demnew_daily_am_all_data', 'U') IS NOT NULL 
DROP TABLE dbo.MAT_v_demnew_daily_am_all_data 
SELECT INTO dbo.MAT_v_demnew_daily_am_all_data FROM dbo.v_demnew_daily_am; 

After you create it:

EXEC [procedure_name];