SQL Server append query

Question

I have the following (not working) query:

insert into [MyDB].[dbo].[Reports_StepsStat]  
    (ActivityID,TaskID,StepIndex,StepStatus,TimeSpanInSeconds,Score)
VALUES (
  SELECT
    tasks.ActivityID as ActivityID,
    tasks.ID as TaskID,
    [StepIndex]=item.value('(StepIndex)[1]', 'NVARCHAR(MAX)'),
    [StepStatus]=item.value('(Status)[1]', 'NVARCHAR(MAX)'),
    [TimeSpanInSeconds] = DATEDIFF(MINUTE, item.value('(StartedOn)[1]',    'datetime'),item.value('(FinishedOn)[1]', 'datetime')),
    tasks.Score as Score
  FROM 
    [MyDB].[dbo].[Tasks] as tasks 
  CROSS APPLY 
    [Progress].nodes ('//Progress/Steps/ProgressStep') Progress(item)
)

The inner select query (SELECT task.ActivityID..) works perfectly and produces the expected table.

The outer insert into part is supposed to append the result of the inner part to a table by the name of Reports_StepsStat. This does not work.

I have tried and succeeded doing that with SELECT INTO, but apparently SELECT INTO can only be used to create a new table, and not to append to an existing table, which is what I need.

The errors I get are:

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ')'.

Yogendra Singh · Accepted Answer · 2012-11-05 16:01:47Z

6

I think VALUES ( is not required in your query.

 insert into [MyDB].[dbo].[Reports_StepsStat]  
 (ActivityID,TaskID,StepIndex,StepStatus,TimeSpanInSeconds,Score)
 SELECT
  tasks.ActivityID as ActivityID,
  tasks.ID as TaskID,
  [StepIndex]=item.value('(StepIndex)[1]', 'NVARCHAR(MAX)'),
  [StepStatus]=item.value('(Status)[1]', 'NVARCHAR(MAX)'),
  [TimeSpanInSeconds]=DATEDIFF(MINUTE,item.value('(StartedOn)[1]', 'datetime'),
  item.value('(FinishedOn)[1]', 'datetime')),
  tasks.Score as Score
 FROM [MyDB].[dbo].[Tasks] as tasks 
     CROSS APPLY [Progress].nodes ('//Progress/Steps/ProgressStep') Progress(item)

answered Nov 5, 2012 at 16:01

Yogendra Singh

34.5k7 gold badges66 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

pbhd · Accepted Answer · 2012-11-05 16:01:45Z

3

Syntax is

insert into a select * from b

so just omit the values (...) surroundng the select...

answered Nov 5, 2012 at 16:01

pbhd

4,4671 gold badge23 silver badges26 bronze badges

Collectives™ on Stack Overflow

SQL Server append query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related