Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010

    Unanswered: OUTPUT Statement

    Hi All

    I am trying to use an OUTPUT statement on my database in order to capture any changes made by the user on a specific table and log it into another table.

    I have a couple of questions where I am getting an error and I would be grateful if anyone can help me.

    1. Why cant I use the SQL Server keyword $ACTION to capture what the action of the change was i.e. INSERTED or DELETED. It seems to come up with an error when trying to use this keyword.

    2. When trying to capture both INSERTED and DELETED actions in my table, it comes up with an error (which is why I have commented out my 'del' code). I understand that im not actually deleting anything in the below query but I would just expect it to return a NULL value in my declared table.

    DECLARE @test_table_output TABLE
    	action_type VARCHAR(25), 
    	ins_lead_id INT, 
    	--del_lead_id INT,
    	ins_fname VARCHAR(25),
    	--del_fname VARCHAR(25), 
    	ins_sname VARCHAR(25),
    	--del_sname VARCHAR(25)
    INSERT INTO dbo.leads(lead_id, fname, sname)
    INTO @test_table_output
    VALUES(5,'james','smith'), (6,'harry','mark')
    SELECT * FROM @test_table_output
    Thank you.

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    1) $ACTION is NOT a Microsoft SQL Keyword

    2) See #1 above.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2004
    Provided Answers: 4
    From BOL:

    Is available only for the MERGE statement. Specifies a column of type nvarchar(10) in the OUTPUT clause in a MERGE statement that returns one of three values for each row: 'INSERT', 'UPDATE', or 'DELETE', according to the action that was performed on that row.
    This will get you started with OUTPUT in combination with MERGE and $action.

    You can find more about the problems you encountered in BOL:
    DELETED cannot be used with the OUTPUT clause in the INSERT statement.
    With kind regards . . . . . SQL Server 2000/2005/2012

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jun 2010
    Thanks guys.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts