Search code examples
mysqlsql-serversql-insertselect-into

Select Into/Insert Into SQL Server query duplicates

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

Solution

  • 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];