SQL Server 2005 Express edition Output keyword syntax

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.

  • Echo JSON in a d3.js chart
  • How to check existence of user-define table type in SQL Server 2008?
  • Change Schema Name Of Table In SQL
  • Simplest solution for high availability of SQL server 2008?
  • NOT IN statement for Visual Studio's Query Builder for TableAdapter
  • Getting remote database data into local database in android
  • 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).

    2 Solutions collect form web for “SQL Server 2005 Express edition Output keyword syntax”

    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)

    Output Inserted. doesnot work with Express edition.

    Try with @@IDENTITY instead

    Replace the script

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

    With

    DECLARE @TempDate DATETIME
    
    INSERT INTO #tmp_test (RecordCreation)
    VALUES ('2009-10-02')
    
    SET @TempDate = @@IDENTITY
    
    SELECT @TempDate
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.