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.

  • How to get distinct record from two tables in sql?
  • Installing SQL Server 2014 express on Windows server 2012 box
  • Image is not saved to database
  • Incorrect syntax near 'GO'
  • Removing Database References from an ASP.NET MVC Site
  • Unpivot with different columns
  • 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.