INSERT OUTPUT

  • Comments posted to this topic are about the item INSERT OUTPUT

  • Going back to the basics - Nice question

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Nice Question.

    -----------------
    Gobikannan

  • Good Question !!!.

  • Great question. I was looking for a trap, but apparently there wasn't any 😀

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (2/1/2012)


    Great question. I was looking for a trap, but apparently there wasn't any 😀

    Me too. I was expecting:

    GO

    CREATE TABLE dbo.Test

    (

    ID integer IDENTITY PRIMARY KEY,

    AuditDateTime datetime NOT NULL

    );

    GO

    CREATE TRIGGER [trg dbo.Test IOI AuditDateTime]

    ON dbo.Test

    INSTEAD OF INSERT

    AS

    BEGIN

    SET ROWCOUNT 0;

    SET NOCOUNT ON;

    INSERT dbo.Test

    (AuditDateTime)

    SELECT

    GETUTCDATE()

    FROM INSERTED AS ins

    END;

    DECLARE @Output TABLE (ID integer, AuditDateTime datetime);

    INSERT dbo.Test

    OUTPUT INSERTED.*

    INTO @Output

    DEFAULT VALUES;

    -- Zero and NULL!

    SELECT * FROM @Output AS o;

    GO

    DROP TABLE Test;

  • This was removed by the editor as SPAM

  • Thanks for a nice and easy question.

    Sometimes it pays to "process" the newsletter from top to bottom, as there was an informative post[/url] by one Steve Jones :laugh:.

    Cheers,

    Michael

  • DATE is not a valid data type... ?

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

  • BenWard (2/2/2012)


    DATE is not a valid data type... ?

    It was introduced for SQL Server 2008, so I guess the question should read "On SQL Server 2008 and above, what is the output of this code?"


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • good one.

  • Ah cool. As expected it worked fine on 2k5 when I set the data type to DATETIME

    Looks like a really useful funtion. I shall be using it a lot I'm sure!

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

  • I like using this function for audit tables. If you have an application that allows users to add, edit, and delete, you can use this method to keep track of the changes performed by those users. Nice if you are asked to provide information such as who updated the record and what was changed.

  • Cheers Steve, I was looking for the trap too!

    I love this feature, actually currently writing lots of them for a data migration project.

  • Simple question - thanks.

    No trap - thanks.

    Points today - thanks

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a questionThere are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan I would never join a club that would allow me as a member - Groucho Marx

Viewing 15 posts - 1 through 15 (of 63 total)

You must be logged in to reply to this topic. Login to reply