SQL Server 2005 Express edition Output keyword syntax

Question

I'm playing with the Output keyword in SQL Serer 2005 Express. I've written the following query:

Declare @tempTable as Table(masterLotDB datetime)
Insert  into dbo.tblMasterLot (RecordCreation)
Values ('2009-10-02')
OUTPUT INSERTED.RecordCreation
into @tempTable

I get a syntax error of Msg 102, Level 15, State 1, Line 6 Incorrect syntax near 'OUTPUT'. I've tried various combinations.

The query works without the OUTPUT stuff (i.e. it puts a new record in tblMasterLot, There is nothing special about tblMasterLot.RecordCreation other than being defined 'not null'. It's not a primary key or identity.

1. What's going on? Any chance 'OUTPUT' is not really supported in SQL Express 2005?
2. Any tutorials on OUTPUT? (especially in conjuntion with Insert).

Answer 1

The OUTPUT clause should come before VALUES

See Example A here

Following up: I reproduced your original 'incorrect syntax near OUTPUT' error using your code. However, by changing the order of the clauses, it works fine. Here's my code:

create table #tmp_test (recordCreation datetime)

Declare @tempTable as Table(masterLotDB datetime)

Insert  into #tmp_test (RecordCreation)
OUTPUT INSERTED.RecordCreation into @tempTable
Values ('2009-10-02')

Output: (1 row(s) affected)