sql-serverinsertcopy

How to insert a query result into a new table


I would like to insert the query result into a new table to manipulate it easily, right now I can get the query result but I'm not able to insert it into a new table, this is the query I want to insert into:

with Errors as 
(
    select [NOW], (left([ERROR],10)) as Error 
    from [MESF_2].[dbo].[Machine_Error]

    union all

    select [NOW], [cce] 
    from [MESF_2].[dbo].[Server]
)
select 
    [now], [error], 
    datediff(second, LAG([now],1,null) over (order by [now] asc), [now]) as [Delta in seconds] 
from Errors


Create table [MESF_2].[dbo].[MESF2_Data_Summary] 
(
    [Now] datetime, 
    [Error] nvarchar(255),
    [Delta in seconds] integer 
);

I want to insert the results into the table [MESF2_Data_Summary]


Solution

  • Create the table first, then run:

    with Errors as 
    (
        .... (the CTE from your post) ....
    )
    insert into [MESF_2].[dbo].[MESF2_Data_Summary] (now, error, [Delta in seconds])
        select
            [now], [error], 
            datediff(second, LAG([now],1,null) over (order by [now] asc), [now]) as [Delta in seconds] 
        from Errors